0

i am using navicat8 for MySQL for writing trigger, bellow is my trigger statement.

insert into two(name,curdate()) 
select name from one

but it will display error while saving trigger.

John Woo
  • 238,432
  • 61
  • 456
  • 464
ishwar
  • 444
  • 6
  • 20

2 Answers2

0

You should specify the name of the column instead of passing CURDATE().

Use the following syntax,

insert into two (name, colName) 
select name, CURDATE() 
from one

UPDATE 1

So here's what you are going to do.

  • You need to add UNIQUE constraint on column Name on table two.

To alter the table,

ALTER TABLE tableNamehere ADD CONSTRAINT two_uq UNIQUE(name);
John Woo
  • 238,432
  • 61
  • 456
  • 464
  • insert into two(name, date) select name, curdate() from one on duplicate key update name=values(name) – ishwar Dec 29 '12 at 11:21
  • @ishwar what's your question? `:D` – John Woo Dec 29 '12 at 11:22
  • i wrote this above and its will insert only if there is not same entry in table "two" but i need to check that date field same then i need to update.. – ishwar Dec 29 '12 at 11:23
  • i seen but i need to check date field contains duplicate or not. – ishwar Dec 29 '12 at 11:54
  • because i am implementing this concept in Myweb site for storing the daily stock details – ishwar Dec 29 '12 at 11:56
  • so need to check first if its todays date data allready exist then i need to update.else i need to insert – ishwar Dec 29 '12 at 11:57
  • i need to give where condition but its showing errors. the code is insert into closingstockt(CLS_BSID,CLS_Qty,CLS_SQty,CLS_CDate) select BS_ID as CLS_BSID,BS_Qty as CLS_Qty,BSS_Qty as CLS_SQty,curdate() from barstockt on duplicate key update CLS_Qty=BS_Qty,CLS_SQty=BSS_Qty where CLS_BSID=BS_ID – ishwar Dec 29 '12 at 13:23
  • the `WHERE` clause should come before `...on duplicate key update...` – John Woo Dec 29 '12 at 13:24
  • if i wrote this way then its check in barstockt table and i need to give where clause in closingstockt insert into closingstockt(CLS_BSID,CLS_Qty,CLS_SQty,CLS_CDate) select BS_ID as CLS_BSID,BS_Qty as CLS_Qty,BSS_Qty as CLS_SQty,curdate() from barstockt where CLS_BSID=BS_ID on duplicate key update CLS_Qty=BS_Qty,CLS_SQty=BSS_Qty – ishwar Dec 29 '12 at 13:33
  • you need to do it on separate query. – John Woo Dec 31 '12 at 06:29
0
insert into two(name, date_col) 
select name, curdate()
from one

First you have to name the columns you want to insert into, and then the values. If you want to fill all columns from your table you can leave the column name part and do

insert into two 
select name, curdate()
from one
juergen d
  • 186,950
  • 30
  • 261
  • 325
  • sir, i having one question ..i need to insert data on table name "two" from "one" only if todays's date is not exist else if todays date exist then i need to update data. so how i implement ? – ishwar Dec 29 '12 at 10:51
  • Have a look at the answers [here](http://stackoverflow.com/questions/4205181/insert-to-table-or-update-if-exists-mysql) – juergen d Dec 29 '12 at 10:56
  • i seen that post.but i need to check the date column. – ishwar Dec 29 '12 at 11:28
  • insert into two(name, date) select name, curdate() from one on duplicate key update name=values(name) – ishwar Dec 29 '12 at 11:30
  • how to check date column only.if its exist then i need to update else i need to insert – ishwar Dec 29 '12 at 11:30
  • insert into closingstockt(CLS_BSID,CLS_Qty,CLS_SQty,CLS_CDate) select BS_ID as CLS_BSID,BS_Qty as CLS_Qty,BSS_Qty as CLS_SQty,curdate() from barstockt on duplicate key update CLS_Qty=BS_Qty,CLS_SQty=BSS_Qty where CLS_BSID=BS_ID this statement cause error because of where condition, plz help me where i need to write where condition? actually i need to check where condition while updating row – ishwar Dec 31 '12 at 07:26
  • Write another question about it. – juergen d Dec 31 '12 at 14:43