-1

I have: Select some set of pairs. The first column is the id of row in table, the second is the new value which should be assigned to that row.

-- the first query
CREATE TABLE tmp;
SELECT row_id, new_value
FROM [not essential tables and joins];  //PSEUDOCODE

-- another queries
FOR EACH tmp //PSEUDOCODE
  UPDATE table SET value = new_value WHERE id = row_id;

-- QUESTION: CAN I MERGE SELECT AND UPDATE IN ONE QUERY? 
-- I want avoid creating temporary table.

Problem: Iteration through table (as in example above) decrease clearness and speed of code.

Question: *How to do the same in single query

VB_
  • 45,112
  • 42
  • 145
  • 293

1 Answers1

3

I think you are looking for update the table join with other table (Not sure though). You can do something like 

UPDATE tmp a 
    JOIN sometable b ON a.col = b.col 
    AND a.id = b.row_id 
SET a.value = b.new_value
Rahul
  • 76,197
  • 13
  • 71
  • 125