CODEWITHSUNDEEP
sqloracle-databaseplsql
GxFlint
GxFlint

Reputation: 294

UPDATE on multiple rows from SELECT

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.

Upvotes: 1

Views: 116

Answers (1)

Hamidreza
Hamidreza

Reputation: 3118

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
);

Upvotes: 2

Related Questions