CODEWITHSUNDEEP
oracle-database
LeoEY
LeoEY

Reputation: 29

Update tables value by comparing with another table

Need help with my oracle sql code. It look easier but somehow i just couldn't get it right. I have two table A & B. Table A has procedure that insert data from Table B everyday. But the problem is Table B data can be changed by user and once it change, it does not reflect in my Table A (as highlight in yellow). I just want to update the highlighted value only. User can be changing any data from all the column thats why i include all the column in my codes

update tableA a set (a.date,a.id,a.sales,a.profit,a.loss) = (select b.date,b.id,b.sales,b.profit,b.loss from tableB b where a.date=b.date and a.id=b.id and a.sales=b.sales and a.profit=b.profit and a.loss=b.loss) where a.date = (select b.date from tableB b) and a.id != (select b.id from tableB b) and a.sales !=(select b.sales from tableB b) and a.profit != (select b.profit from tableB b) and a.loss != (select b.loss from tableB b)

enter image description here

Upvotes: 0

Views: 779

Answers (2)

Popeye
Popeye

Reputation: 35920

Using the UPDATE statement, You can achieve the desired result using the following SQL:

UPDATE TABLEA A
SET
    ( A."DATE", A.ID, A.SALES, A.PROFIT, A.LOSS ) = (
        SELECT
            B."DATE", B.ID, B.SALES, B.PROFIT, B.LOSS
        FROM TABLEB B
        WHERE A."DATE" = B."DATE"
    )
WHERE
    EXISTS (
        SELECT 1
          FROM TABLEB B
         WHERE A."DATE" = B."DATE"
          AND ( A.ID != B.ID 
                OR A.SALES != B.SALES
                OR A.PROFIT != B.PROFIT
                OR A.LOSS != B.LOSS )
    );

Tip: Avoid giving oracle reserved keywords (In your case DATE) as the column name of the table.

Cheers!!

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 143013

Would merge help? It is capable of inserting new rows and updating existing ones. For example:

merge into tablea a
  using (select b.date, b.id, b.sales, b.profit, b.loss
         from tableb b
        ) x
  on (a.date = x.date)
when matched then update set
  a.id = x.id,
  a.sales = x.sales,
  a.profit = x.profit,
  a.loss = x.loss
  where a.id <> x.id
     or a.sales <> x.sales
     or a.profit <> x.profit
     or a.loss <> x.loss
when not matched then insert (date, id, sales, profit, loss)
  values (x.date, x.id, x.sales, x.profit, x.loss)

Upvotes: 0

Related Questions