Update from select query in mysql

Question

I have used the following query to insert values in table

INSERT INTO `tbl1` SELECT * FROM tbl2

Over here tb1 is a temp table.

Now, I want something like this

UPDATE `my_table` SELECT * FROM tbl1

I know that the syntax for update is Update tbl SET cols = vals But can we have something like the insert query above ?

Thanks.

Answer 1

You can do something like this:

update my_table join tbl1 on my_table.id = tbl1.id
set my_table.Vaal= tbl1.vaal 

Answer 2

You can doInsert with Select but not Update with Select. But still possible by using JOIN within UPDATE.

UPDATE table1 t1 JOIN table2 t2 ON t1.id = t2.id
SET t1.col1 = t2.col2, t1.col2 = t2.col2

Answer 3

You can join your tbl1 table with my_table using the multiple-table UPDATE syntax:

UPDATE my_table JOIN tbl1 ON ***join_condition***
  SET my_table.foo = tbl1.bar, ...