If I have a table Emp and I want the address id of row 2 to be the same as the address id of row 1, can I do it without a stored procedure?
Something like
UPDATE Emp SET address_id = (SELECT address_id FROM Emp WHERE id = 1) WHERE id = 2;
Maybe I can do something like update Emp e1, Emp e2 set ... ?
Thanks
This should work for you mate.
UPDATE Emp t, (SELECT address_id
FROM Emp
WHERE ID = 1) t1
SET t.address_id = t1.address_id
WHERE ID = 2
Your update can work(on other databases), but to MySQL's insistence that a table can't update itself, i.e. you can't do this:
update tbl
set address = (select address from tbl where name = 'paul')
where name = 'george';
But you can do a work-around: http://www.sqlfiddle.com/#!2/5f373/6
update tbl
set address = ( select address from
(select address from tbl where name = 'paul') x )
where name = 'george';
Anyway, you should go with Gregology's answer. That is future-proof, you can use it when you want to update two or more fields. Gregology's answer demo in sqlfiddle: http://www.sqlfiddle.com/#!2/5f373/10
try this one.
UPDATE Emp SET address_id = (SELECT t.address_id FROM Emp t WHERE t.id = 1) WHERE id = 2
-- aliasing of tables can only be done thru the subquery of an update statement.
Rhian A. (NZT)
In fact, the query you posted should work. This feature is called subqueries.
