Update based on select data

Question

I have a query which returns

Primary Key | Value

And I want to update my data where primary key = primary key and value = another value. So basically I have

SELECT
    id,
    custom_value
FROM custom
JOIN user
USING (user_id)
WHERE id = 45
AND custom_name = "stuff";

And this generates

id | custom_value
1  | stuff
2  | stuff 2

And I want to then UPDATE the existing db with

UPDATE table SET field = custom_value WHERE id = id;

Or if I used the first row it would be

UPDATE table SET field = 'stuff' WHERE id = 1;

How could I do this?

So...

SELECT                          UPDATE table
    id,               ->        WHERE id = id
    custom_value      ->        SET field = custom_value
FROM custom
JOIN user
USING (user_id)
WHERE id = 45
AND custom_name = "stuff";

Select data then update another table with that data.

Existing db? Is the query above coming from a different database, or are you wanting to update one of the tables based on the others value? — Rwd, Sep 25 '14 at 16:09
If I understand you correctly this answer should help: http://stackoverflow.com/questions/11588710/mysql-update-query-with-sub-query — Rwd, Sep 25 '14 at 16:18

score 0 · Accepted Answer · answered Sep 25 '14 at 16:20

Two steps:

create temporary table tmp_tbl as select .... your select goes here;

then

update table, tmp_tbl
set table.field = tmp_tbl.custom_value
where table.id = tmp_tbl.id

and then, optionally, to clean up the unneeded data:

drop table tmp_tbl

(Or let MySQL drop it automatically on session closure.)

Update based on select data

1 Answers1