-1

The following code is a trigger I wrote in Navicat for Mysql. The inserting part is working correctly but for update I need to update a specific row quantity based on id. How do I write the where clause in the update sql query.

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
Jonathan Hall
  • 75,165
  • 16
  • 143
  • 189
ishwar
  • 444
  • 7
  • 20

1 Answers1

0

ON DUPLICATE KEY UPDATE will update the row which already exist in the database. For example:

INSERT INTO table (field,field2) VALUES ('data1','data2')
ON DUPLICATE KEY UPDATE lastupdate=NOW()

EDIT: I suggest to use IF()

INSERT ... ON DUPLICATE KEY UPDATE with WHERE?

Community
  • 1
  • 1
Jordi Kroon
  • 2,607
  • 3
  • 31
  • 55
  • i need update row on bases id is same. like following code but where clause is not allow in On Duplicate Key Update. 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 Jan 02 '13 at 13:19
  • how i do not know.how to give if condition so can u help for that code – ishwar Jan 02 '13 at 13:27
  • i check that link but i did not find what actually i am looking. i need to update all fields base on the id matching in both tables.but i check above link there is no checking that if id is same that row only update not all. – ishwar Jan 03 '13 at 07:07