Nested SQL query using CASE statements

How can I write just one SQL query to get the same result by perform these two queries (steps):

First Step:

SELECT old_id, new_id FROM history WHERE flag = 1;

Result:

+--------+--------+
| old_id | new_id |
+--------+--------+
|     11 |     22 |
|     33 |     44 |
|     55 |     66 |
+--------+--------+

And then, using previous res开发者_如何转开发ults, execute this query:

UPDATE other_tabla SET somefk_id = CASE somefk_id
    WHEN 11 THEN 22
    WHEN 33 THEN 44
    WHEN 55 THEN 66
END WHERE somefk_id IN (11,33,55)

I think this is what you're describing:

UPDATE `other_tablea`
JOIN `history` ON history.old_id = other_tablea.somefk_id AND history.flag = 1
SET other_tablea.somefk_id = history.new_id

A subquery would appear to do the trick:

update  other_tabla
set     somefk_id = coalesce((
            select  new_id 
            from    history 
            where   flag = 1 
                    and old_id = other_tabla.somefk_id
        ), other_tabla.somefk_id)

you don't need case

update
  other_table, history 
set
  other_table.somefk_id=history.new_id
where 
  other_table.somefk_id=history.old_id and history.flag=1;

You can use a temporary table to store de results of the first Query and then resuse the data in the second query.

SELECT old_id, new_id 
INTO #tmpTable1
FROM history 
WHERE flag = 1;

UPDATE other_tabla SET somefk_id = t.new.id
FROM other_tabla as o
INNER JOIN #tmpTable1 t ON o.somefk_id=t.old_id

DROP TABLE #tmpTable1