UPDATE on multiple rows from SELECT

Question

I've tried the following query, it updates with the correct value all the rows that I want. But there is a problem, it will set to NULL all the other rows on the table table_destiny

UPDATE table_destiny t_d
SET (t_d.a, t_d.b, t_d.c) 
      =
    (SELECT a, b, c 
     FROM (SELECT DISTINCT t_d.a, t_s.a, t_s.b 
           FROM table_source t_s
           JOIN table_destiny t_d ON t_d.id = s.id) t_s
     WHERE t_s.id = t_d.id AND ROWNUM <= 1
    )
WHERE EXISTS (
  SELECT DISTINCT t_d.id
  FROM (SELECT DISTINCT t_d.a, t_s.a, t_s.b 
        FROM table_source t_s
        JOIN table_destiny t_d ON t_d.id = s.id) t_s
  WHERE t_s.id = t_d.id
);

Explanation asked by @Thorsten_Kettner:

On a separate Owner I have table_source, now called t_children, that has the data that I want. Many children belong to the same group on table t_parent, old table_destiny, but they are on unrelated Owners with no strong relationship between them. I can get to the parent row of each child after a few joins with distinct.

So, I got the data that I want and the rows that I want to update them into.

How do I write an UPDATE that say: Update ONLY "this specific rows" with the data from "that select"?

I'm looking for something like this answer, but for Oracle.

Can you just change your update to set nulls and replace `WHERE EXISTS` with `WHERE NOT EXISTS`? — Andrew, Nov 16 '13 at 17:40
I don't think I understood your suggestion. I don't want the data that doesn't fall in the `WHERE EXISTS (...);` to be touched. — GxFlint, Nov 16 '13 at 17:45
Please explain your tables. Both have a column called id, but it's not really the id of their rows? So there can be many records for the same id? What are the primary keys of your tables? (BTW: table_destiny is a funny name for a destination table.) — Thorsten Kettner, Nov 16 '13 at 18:14

score 2 · Answer 1 · answered Nov 17 '13 at 05:17

i think that your query is correct, it just need a little changes, try this:

UPDATE table_destiny t1
SET (t1.a, t1.b, t1.c) 
      =
    (SELECT a, b, c 
     FROM (SELECT DISTINCT t_d.a, t_s.a, t_s.b 
           FROM table_source t_s
           JOIN table_destiny t_d ON t_d.id = s.id) t2
     WHERE t2.id = t1.id AND ROWNUM <= 1
    )
WHERE EXISTS (
  SELECT DISTINCT t_d.id
  FROM (SELECT DISTINCT t_d.a, t_s.a, t_s.b 
        FROM table_source t_s
        JOIN table_destiny t_d ON t_d.id = s.id) t2
  WHERE t2.id = t1.id
);

UPDATE on multiple rows from SELECT

1 Answers1