How to update the target table

[dongyingying]

Hi all,

I want to update the target table ,But maybe the data in target table not exists . in the job i Want not to use the join or lookup and so on.
so before I update the data I need to judge if there is exists data which i want to update.

I write like this :

update dw1.ethan_ssa_sr set sde_dtrd=? where dtke_dkg_id =? and exists (select 1 from dw1.ethan_ssa_sr where dtke_dkg_id =? )
But as we know there are two columns in columns tab, and in the sql I use three .so there is a error message like :
[IBM][CLI Driver] CLI0100E Wrong number of parameters. SQLSTATE=07001

So anybody can help me out ? if you can do that pls write down the sql ,thanks !

[dongyingying]

Here is the detail error logs:

[IBM][CLI Driver][DB2/AIX64] SQL0100W No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. SQLSTATE=02000

[kwwilliams]

Why would you do this? Why not just run the update statement? its going to behave the same whether you have the exists logic embedded in it or not. It looks like you are making your job more difficult than it needs to be. If you are writing a parallel job, then you need to be using th e ORCHESTRATE.dieldname for your parameterss -- not question marks.

[dongyingying]

update dw1.ethan_ssa_sr set sde_dtrd=? where dtke_dkg_id =? and exists (select 1 from dw1.ethan_ssa_sr a where a.dtke_dkg_id =dtke_dkg_id )
if changed to this one ,what do you think kwwilliams?

[kwwilliams]

Your missing my point, your update is not efficiently written. An update statement with a where clause will only execute if the condition on a row where the where clause is true. In this case you have written a statment that says:

update dw1.ethan_ssa_sr set sde_dtrd=? where dtke_dkg_id =? and exists (select 1 from dw1.ethan_ssa_sr a where a.dtke_dkg_id =dtke_dkg_id )

the table in both the update and select in the where clause table are both the same and both have the same condition. In other words you are checking 2 times to ensure that the condition of dtke_dkg_id is met. You only need to write:

update dw1.ethan_ssa_sr set sde_dtrd=? where dtke_dkg_id =?

You don't need the exists statement since it is checking the exact same condition as the where clause.

[dongyingying]

Thanks kwwilliams,I got you.