updating a DB table without looping

[praveenraju]

Hi,

I am reading an excel sheet which contains customer_accoun t_numbers
and creating a datable using it.

Assume the datatable has values 100,200,300.

Now i want to update a CustomerStatus Database table (I am using DB2)
and want to update the CUST_STATUS field in this table to a value 4 (inactive) only for these account numbers 100,200,300..

Note that i created the datatable in step 1 from an Excel sheet

I need to update the DB table for the cust_status field with a value 4 in CustomerStatus DB table without any looping..

is this possible or i need to go with a loop only

Thanks

[DrBunchman]

Hi praveenraju,

I'm not sure of the DB2 syntax but something like the following SQL should do the trick:

UPDATE CustomerStatus SET CUST_STATUS = 4 WHERE AccountNumber IN (100, 200, 300)

Does this help?

Dr B

[praveenraju]

Hi praveenraju,

I'm not sure of the DB2 syntax but something like the following SQL should do the trick:

UPDATE CustomerStatus SET CUST_STATUS = 4 WHERE AccountNumber IN (100, 200, 300)

Does this help?

Dr B

Hi,
Thanks for the information.

But for creating ths IN clause i need to again iterate my datatable isnt it?

I want to directly use my datatable instead of doing any kind of looping. would that be possible

Pls let me know your thoughts

[DrBunchman]

You can use a sub query.

UPDATE CustomerStatus SET CUST_STATUS = 4 WHERE AccountNumber IN (SELECT AccountNumber FROM AccountTable WHERE etc etc)

Put whatever query returns the account numbers that you wish to update inside the sub query and it will update all of those.

Hope this helps,

Dr B