CODEWITHSUNDEEP
mysql
Namrata Shrivas
Namrata Shrivas

Reputation: 77

How to update one table from another table?

I want to update rows from one table to another, both have same structure and i use this query

UPDATE bh_master_ritesh_stock AS st
SET st.tag_uid = apprv.tag_uid ,st.stk_id = apprv.stk_id
FROM bh_stock_approval AS apprv
WHERE st.stock_id = apprv.stock_id

but it gives me this error,

You have an error in your SQL syntax; check the manual that corresponds to 
your MySQL server version for the right syntax to use near 
'FROM bh_stock_approval as apprv WHERE st.stock_id = apprv.stock_id' at line 3

where is my mistake? or am I using wrong query?

Upvotes: 0

Views: 72

Answers (4)

Tabish
Tabish

Reputation: 1702

UPDATE bh_master_ritesh_stock A
SET A.tag_uid, A.stk_id =( select B.tag_uid, B.stk_id
FROM bh_stock_approval B WHERE A.stock_id = B.stock_id)
WHERE EXISTS(SELECT '1' FROM bh_stock_approval B1 WHERE A.stock_id =B1.stock_id)

Upvotes: 0

Ryan-Neal Mes
Ryan-Neal Mes

Reputation: 6263

Try this.

UPDATE bh_master_ritesh_stock st
INNER JOIN bh_stock_approval apprv on
  st.stock_id = apprv.stock_id
SET 
  st.tag_uid = apprv.tag_uid ,st.stk_id = apprv.stk_id

Upvotes: 0

Luca Davanzo
Luca Davanzo

Reputation: 21520

You syntax is simply wrong, "FROM" is unexpected:

UPDATE bh_master_ritesh_stock AS st,bh_stock_approval AS apprv
SET st.tag_uid = apprv.tag_uid ,st.stk_id = apprv.stk_id 
WHERE st.stock_id = apprv.stock_id

For other examples that involves more tables, take a look here.

Upvotes: 1

Ronald Alexander Kailola
Ronald Alexander Kailola

Reputation: 149

try this code :

UPDATE bh_master_ritesh_stock AS st,bh_stock_approval AS apprv
SET st.tag_uid = apprv.tag_uid ,st.stk_id = apprv.stk_id 
WHERE st.stock_id = apprv.stock_id

because UPDATE cant use From clause

Upvotes: 1

Related Questions