CODEWITHSUNDEEP
mysqlsqlsql-update
Saint Robson
Saint Robson

Reputation: 5525

MySQL Update Colum Based On Other Column

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 |
+----+--------+

Upvotes: 2

Views: 36

Answers (2)

Mureinik
Mureinik

Reputation: 312344

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

Upvotes: 2

Giorgos Betsos
Giorgos Betsos

Reputation: 72235

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

Upvotes: 2

Related Questions