How to update one table from another table?

Question

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?

score 1 · Accepted Answer · answered Apr 22 '14 at 07:35

1

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

answered Apr 22 '14 at 07:35

Ronald Alexander Kailola

149
9

the question is where the mistake.. if the question is how to effectively the query is should rather use a join :) – Ronald Alexander Kailola Apr 22 '14 at 07:47

score 1 · Answer 2 · edited May 23 '17 at 12:05

1

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.

edited May 23 '17 at 12:05

Community

1
1

answered Apr 22 '14 at 07:37

Luca Davanzo

21,000
15
120
146

score 0 · Answer 3 · answered Apr 22 '14 at 07:41

0

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

answered Apr 22 '14 at 07:41

Ryan-Neal Mes

6,003
7
52
77

score 0 · Answer 4 · answered Apr 22 '14 at 07:42

0

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)

answered Apr 22 '14 at 07:42

Tabish

1,592
16
13

How to update one table from another table?

4 Answers4