CODEWITHSUNDEEP
sqloracle-database
user2862073
user2862073

Reputation: 254

compare two column of a tableand if not matched then update in oracle sql

I have a table test5.having 10000 records.there is two column in table called es_id and cac_id.I want to make these column exact match.if its not macthed the update cac_is with es_id.

for example:

table test5

es_id      cac_id
xxx         xxx
yyy         yyy
zzz         abc
ddd         null
bbb         zzz

output should be :

es_id      cac_id
xxx        xxx
yyy        yyy
zzz        zzz
ddd        ddd
bbb        bbb

test5 table has 100000 records so update the table manually is vary deficult.Please suggest the better way to do this.

Please assist.Thanks!

Upvotes: 1

Views: 116

Answers (3)

Filipe Silva
Filipe Silva

Reputation: 21657

You can simply do:

UPDATE test5
SET es_id = CASE WHEN es_id IS NULL THEN cac_id 
                 ELSE es_id END,
    cac_id = CASE WHEN cac_id <> es_id OR cac_id IS NULL THEN es_id
                  ELSE cac_id END
WHERE cac_id <> es_id
  OR cac_id IS NULL
  OR es_id IS NULL;

This assumes that when es_id is null, cac_id is not, and the other way around

sqlfiddl demo

Upvotes: 0

DCookie
DCookie

Reputation: 43533

How about 

UPDATE test5
   SET cac_id = es_id;

100K records isn't all that much, and you want them all the same anyway, so why check for the condition? Assuming this isn't a recurring process, of course.

Upvotes: 1

vhadalgi
vhadalgi

Reputation: 7189

    update table
    set es_id =cac_id OR cac_id=es_id
where es_id <> cac_id

Upvotes: 0

Related Questions