How to update multiple columns with a single select query in MySQL?

Question

Let take a example, there are two tables as bellow.

OldData
-----------
    id
    name
    address

NewData
-----------
    nid
    name
    address

I want to update OldData table with NewData table.

For that purpose I am trying to use the following query:

UPDATE OldData SET (name, address) = (SELECT name, address FROM NewData WHERE nid = 234)
WHERE id = 123

But it gives a syntax error.

What is the proper way of doing what I try?

Praveen Prasannan · Accepted Answer · 2014-03-12 06:19:51Z

5

UPDATE OldData o, NewData n 
SET n.name = o.name, n.address = o.address 
where n.nid=234 and o.id=123;

answered Mar 12, 2014 at 6:19

Praveen Prasannan

7,15310 gold badges53 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

It should be SET o.name = n.name, o.address = n.address.

Stanley Mungai · Accepted Answer · 2014-03-12 06:17:20Z

0

Try this:

Update oldData set name = (select name from newData where nid = 234),address = (select address from newData where nid = 123);

answered Mar 12, 2014 at 6:17

Stanley Mungai

4,15030 gold badges103 silver badges172 bronze badges

Alexander Farber · Accepted Answer · 2014-03-12 07:42:56Z

0

Try This :

UPDATE OldData a,NewData b 
SET a.name = b.name , a.address = b.address 
WHERE a.id=123 AND b.nid = 234

Alexander Farber

23.4k79 gold badges263 silver badges449 bronze badges

answered Mar 12, 2014 at 6:23

1499 bronze badges