MySQL select and update table at same time

Tags: ,



I want to select a value one by one from my MySQL table and using this value get some value from different table. After getting the value I want to update my same table with this value.
Can I select and update the table at the same time?

I want to use Java to loop the table selecting values one by one from table.

Answer

You can set statement to be updatable. Then you can use the setters of the resultset to update any value.

You can also probably solve this in a single sql query but i’ll have to see the tables to create an example.

Like this for instance:

update table_a a
set column_name=(select b.new_column_value from table_b b where b.uid=a.uid)

You can also add a where clause to the update to only perform it on some records in table_a



Source: stackoverflow