Oracle SQL - Update column data using MINUS operator-CodePudding

I've this sql query

(select x.a, x.b, x.c, x.d
from tableX x
where x.a IS NOT NULL
minus 
select y.a, y.b, y.c, y.d
from tableY y);

The above query returns me all tableX data, that is different from tableY. Multiple tuples are returned

Evidence:

When i run above query i get this result:

a	b	c
1	43	65
2	66	333

When i select data from tableY i get this:

a	b	c
1	54	65
2	88	567

tableY data is the correct data, so i want to update all tuples that are returned from the first query(the one with MINUS clause) with the data from tableY.

The expected result, after the update clause, when i select data from tableX should be:

a	b	c
1	54	65
2	88	567

What is the most eficcient way to do this UPDATE clause?

CodePudding user response：

On oracle I find the MERGE syntax much more usable than the UPDATE syntax...

MERGE INTO
  tablex   x
USING
  tabley   y
    ON (y.a = x.a)
WHEN MATCHED THEN UPDATE
  SET
    x.b = y.b,
    x.c = y.c
  WHERE
    LNNVL(x.b = y.b)
    OR
    LNNVL(x.c = y.c)

Edit: added where clause to avoid redundant updates, following comment below.

CodePudding user response：

update tablex
set (b, c) = (select b, c from tabley where tabley.a = tablex.a)
where exists (select 1 from tabley where tabley.a = tablex.a);

To keep transaction size down, add xb <> yb or xc <> yc:

update tablex
set (b, c) = (select b, c from tabley where tabley.a = tablex.a)
where exists (select 1 from tabley
              where tabley.a = tablex.a
                and (LNNVL(tabley.b = tablex.b) or LNNVL(tabley.c = tablex.c)));