Update join two tables in MySql

Question

I've in mysql db two tables:

1) dotable_new;

2) dotable_new_new

Now I need update the value TOTAL in my table dotable_new of value TOTAL in my table dotable_new_new.

I'm trying this update query without success ... can you help me?

UPDATE dotable_new a, dotable_new_new tmp
SET a.total = tmp.total
WHERE
    a.rdt IN ('tot mac')

dotable_new;

+---------+---------------+--------+-------+----+
| RDT     | TYPE          | NUMBER | TOTAL | ID |
+---------+---------------+--------+-------+----+
| tot mac | tot           |   3209 |  3249 |  1 |
+---------+---------------+--------+-------+----+

dotable_new_new

+---------+-------+----+
| RDT     | TOTAL | ID |
+---------+-------+----+
| tot mac | 10899 |  5 |
+---------+-------+----+

The `a.rdt IN ()` returns multiple values. – Jim Mar 29 '14 at 21:58 — Jim, Mar 29 '14 at 21:58

score 1 · Accepted Answer · edited May 23 '17 at 10:31

1

check this will help you on how to join two tables in update

try that:

  UPDATE dotable_new a
  INNER JOIN dotable_new_new tmp ON tmp.rdt = a.rdt
  SET a.total =  tmp.total
  WHERE a.rdt = 'tot mac'

edited May 23 '17 at 10:31

Community

1
1

answered Mar 29 '14 at 22:06

echo_Me

37,078
5
58
78

score 0 · Answer 2 · answered Mar 29 '14 at 21:58

Try this:

UPDATE dotable_new a
SET a.total = (SELECT tmp.total
               FROM  dotable_new_new tmp
               WHERE tmp.rdt = a.rdt)

If you want to update the record rdt = 'tot mac' only, then add the following line to the end:

WHERE a.rdt = 'tot mac'

Update join two tables in MySql

2 Answers2