CODEWITHSUNDEEP
sqloracle-databasedatetimesql-updatesubquery
Eddy L
Eddy L

Reputation: 23

How to i update a column based on comparison with value from another table

I crated a script to update a column based on the result of comparison betwen values in two tables.This script works when i created copies of these tables somewhere else but it updates zero values when i applied it on the live system

I have two tables C1 and C2.Table C2 has some columns includind a column called EXIT_DATE which i need to compare with a column called RECEIPTDATE in table C1 where MEMBER_NUMBERS match and EXIT_DATE is greater than RECEIPTDATE. When the condition is met then i need to set a column USED in table C1 to Y.

UPDATE  SS_CONTRIBUTIONS c1
set C1.USED = 'Y'
 where SCHEME_ID = 1 and rowid <
     (
      select max(rowid)
        from SS_BENEFIT c2
       where c2.MEMBERID= c1.MEMBER_ID
       and c2.EXIT_DATE > c1.RECEIPTDATE
       );commit;

i expect 350000 records to be updated but it updates 0 record

Upvotes: 0

Views: 940

Answers (1)

GMB
GMB

Reputation: 222592

I would advocate againts using rowid here. This pseudo-column represents the address of the row (object number, data block, ...), and I cannot see how it would be useful for what you are trying to accomplish.

I think that a correlated subquery should work just fine for your use case:

update ss_contributions c1 
set c1.used = 'Y' 
where 
    c1.scheme_id = 1
    and c1.receiptdate < (
        select max(c2.exit_date)
        from ss_benefit c2 
        where c2.memberid= c1.member_id
    );

Upvotes: 3

Related Questions