MySQL Update Colum Based On Other Column

Question

I have this table :

table_a
+----+--------+
| id | status |
+----+--------+
| 10 | ERROR  |
+----+--------+

and I also have this table :

table_b
+------------+----------------+
| trading_id | trading_status |
+------------+----------------+
|         10 | CLOSED         |
+------------+----------------+

how to UPDATE table_a.status based on table_b.trading_status so that the result will be like this :

table_a
+----+--------+
| id | status |
+----+--------+
| 10 | CLOSED |
+----+--------+

Answer 1

Try this:

UPDATE table_a
SET status = COALESCE((SELECT trading_status
                       FROM table_b 
                       WHERE trading_id = table_a.id), table_a.status)

The above query assumes there is at most one record in table_b matching each record of table_a.

Demo here

Answer 2

You could use the update-join syntax:

UPDATE table_a
JOIN   table_b ON table_a.id = table_b.id
SET    table_a.status = table_b.trading_status