CODEWITHSUNDEEP
mysqlsql
rdem
rdem

Reputation: 195

Insert content from a temporary table into an update query

What I want to do is update a table by values obtained from a temporary table.

See the layout below for clarification:

I want the username "test" with id = 1, changed to the value of new_id = 2 from the temporary table.

Temporary table:

---------------------------------------------------------
|   username    |   old_id  |   new_id  |   directory   |
---------------------------------------------------------
|   test        |   1       |   2       |   old         |
---------------------------------------------------------

Actual table:

-----------------------------------------
|   username    |   id  |   directory   |
-----------------------------------------
|   test        |   1   |   old         |
|   test        |   2   |   new         |
-----------------------------------------

This is what I have so far, but it's failing pretty badly:

DROP TABLE IF EXISTS TMPRESULT;

CREATE TEMPORARY TABLE IF NOT EXISTS TMPRESULT AS (
  SELECT 
  U.directory AS 'directory',
  U.username AS 'Username',
  U.ID AS 'old_id', 
  (
    SELECT 
    T1.id 
    FROM table1 AS T1
    WHERE U.username = T1.username
    AND T1.directory = 'new'
  ) AS 'new_id'
  FROM table1 AS U
  WHERE U.directory = 'old'
  AND U.username = 'test'
);

UPDATE table2 AS M
SET
  M.id = T.new_id
FROM table2
INNER JOIN TMPRESULT AS T ON M.child_id = T.old_id
WHERE M.username = T.Username

Upvotes: 1

Views: 39

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269513

This is the correct syntax in MySQL:

UPDATE table2 M INNER JOIN
       TMPRESULT T
       ON M.child_id = T.old_id AND M.username = T.Username
    SET M.id = T.new_id;

Upvotes: 2

Related Questions