35

let's say I have select, which return me from table1:

ID  Name
 1  Bob
 2  Alice
 3  Joe

Then I want UPDATE values in another table based on this result:

UPDATE table2 SET Name = table1.Name WHERE ID = table1.ID

As I understood, I can only do internal select in one place, like:

UPDATE table2 SET Name = (select Name from table1) WHERE ...

And I don't know how to specify WHERE-condition.

MasterClass
  • 355
  • 1
  • 3
  • 10

3 Answers3

96

all you should do is just join the tables like this.

UPDATE table2 t2
JOIN table1 t1 ON t1.id = t2.id
SET t2.name = t1.name;

RESULTS WITH JOIN

if you are set on doing it with a select you could do it like this.

UPDATE table2 t2,
(   SELECT Name, id 
    FROM table1 
) t1
SET t2.name = t1.name
WHERE t1.id = t2.id

RESULTS FROM SELECT

John Ruddell
  • 25,283
  • 6
  • 57
  • 86
18
 UPDATE table2
 SET name = (SELECT table1.Name FROM table1 WHERE table1.id = table2.id)
 WHERE apply_condition

EDIT:#1

   UPDATE table2 t2, (SELECT id, name FROM table1) t1 SET t2.name = t1.name WHERE t1.id = t2.id

please read this link,another

Rajib Ghosh
  • 640
  • 3
  • 12
  • 1
    I'm not the downvoter, but just so you know the subquery you have is a correlated subquery meaning it gets executed for every row in table2... which is a bad design in general. and also the WHERE after your SET is redundant – John Ruddell Nov 26 '14 at 17:21
  • 2
    sure thing :) if you changed the query to just select and then set the name it would be a one time select... something like this. `UPDATE table2, (SELECT id, name FROM table1) t1 SET t2.name = t1.name WHERE t1.id = t2.id` – John Ruddell Nov 26 '14 at 17:37
-2

Try this

Update table2
Set Name = (Select Name From table1 where table1.ID = table2.ID)
Where table2.ID In (Select ID From table1)
asantaballa
  • 3,919
  • 1
  • 21
  • 22
  • yes but that also means you are doing a correlated subquery for each row in the other table along with another IN() correlated subquery. the optimization on this is bad. why not just join the table? – John Ruddell Nov 26 '14 at 18:09
  • Some reasons, but some guesses. First, made assumption that since the field names used for join were “ID” that they were probably the primary key and indexed so the optimizer would use the index to find the exact set of rows required, and could probably do that just from the index without having to go into the data itself (except for the actual update change, of course). Second, the join doesn’t work on all DBs, notably SQL Server which uses a special From for that purpose. Even though the question was specifically MySQL I generally try to use platform agnostic queries where I can. ... – asantaballa Nov 26 '14 at 18:26
  • Third, this smelled of a onetime query to correct a problem. If it was a query which was executed regularly a large number of times and the timings/profiler indicated bad performance I’d rework it based on that, but otherwise like I said I’d lean to what works on all platforms. But point taken that this could cause poor performance in some situations. – asantaballa Nov 26 '14 at 18:26