CODEWITHSUNDEEP
sqldb2db2-400
Geoff_S
Geoff_S

Reputation: 5105

Update in DB2 using where exists instead of join

I'm trying to perform an update in DB2, which I started out trying with an inner join, but DB2 won't allow that. I altered it to use a 'where exists' clause but now it's telling me that main_discount in my set clause is not found (I'm assuming because it isn't referenced until the later where exists

How can I make this work as expected if I don't yet have the column value I need?

update main_library.details
set less_amount = ROUND(cast (price as float) * (main_discount/100),2)
    where exists(
        select 
               price,
               wholesale_num,
               main_discount,
               startDate,
               endDate
        from main_library.details D
            inner join 2nd_library.discounts DISC
                on D.wholesale_num = DISC.wholesale_num
            where wholesale_num  = 123
    )
limit 200;

Upvotes: 0

Views: 2449

Answers (1)

nfgl
nfgl

Reputation: 3212

DB2 for i does not allow the UPDATE table FROM that DB2LUW allows

You have two solutions

One is UPDATE using one subquery to select the rows to update, and another to get the value of main_discount

update main_library.details as main
set less_amount = ROUND(cast (price as float) * (
   (select main_discount from 2nd_library.discounts DISC
            where wholesale_num  = main.wholesale_num)
  /100),2)
    where exists(
        select 0 from 2nd_library.discounts DISC
            where wholesale_num  = main.wholesale_num
    )

the other is MERGE

MERGE INTO main_library.details main
using(
select wholesale_num, main_discount from 2nd_library.discounts DISC
) disc on disc.wholesale_num = main.wholesale_num
when matched then
  update set less_amount = ROUND(cast (price as float) * (main_discount)/100),2)

Maybe you should use DECFLOAT rather than FLOAT to avoid suprises

Upvotes: 3

Related Questions