How to Update value for multiple Field in Oracle SQL-CodePudding

I have to update field: Number, Tgl, product in Table Detail_product from table Details with condition:

If, id on Table Detail Product an Detail is same then 
update Detail_product.Tgl using Details.tgl where Details.tgl is null,
update Detail_product.number using Details.number  where Details.number 
update Detail_product.product using Details.product  where Details.product

I have query merge to update field, but i can't create one query to update multiple field

MERGE INTO Detail_product x
USING Details y
ON (x.id = y.id)
  WHEN MATCHED THEN
UPDATE SET x.tgl = y.tgl WHERE Y.tgl IS NOT NULL; 

MERGE INTO Detail_product x
USING Details y
ON (x.id = y.id)
  WHEN MATCHED THEN
UPDATE SET x.number = y.number WHERE Y.number IS NOT NULL; 

MERGE INTO Detail_product x
USING Details y
ON (x.id = y.id)
  WHEN MATCHED THEN
UPDATE SET x.product = y.product WHERE Y.product IS NOT NULL;

What should i do, so the query is sort and the runtime doesn't take long.

CodePudding user response：

You can combine them if you accept some unnecessary column updates (i.e.update the column to it's current value if the new data is null:

MERGE INTO Detail_product x
USING Details y
ON (x.id = y.id)
  WHEN MATCHED THEN
    UPDATE SET x.tgl = coalesce(y.tgl, X.tgl),
        x.number = coalesce(y.number, X.number),
        x.product = coalesce(y.product, X.product)
    WHERE (Y.tgl IS NOT NULL OR Y.number IS NOT NULL OR Y.product IS NOT NULL)

It depends on how many updates are there (Is it costlier to do unnecessary updates or the full join?). Also you need to consider if there are triggers associated with these columns to capture changes (and create superfluous audit records).

CodePudding user response：

you should do the merge only once to improve the performance. first join both the tables and get the right value in a sub query and then update the final table. This will make sure both the tables are read once and marked for updated once. The not null check condition in the sub query can be removed most of the records from details tables are not null.

    MERGE INTO Detail_product x
    USING 
    (select nvl(y.tgl, x.tgl) as tgl, nvl(y.number, x.number) as number, nvl(y.product,x.product) as product
    from Detail_product x inner join Details y on x.id = y.id
    where (y.tgl is not null or y.number is not null or y.product is not null)
    ) y
    ON (x.id = y.id)
    WHEN MATCHED THEN
    UPDATE SET x.tgl = y.tgl, x.number = y.number, x.product = y.product;