Update based on IF Statement 1

[dvannoy]

I really need from the experts on this one...

what I need to do is, create a procedure that updates one field based on about 800 different options. I will run this procedure every hour based on a date field in the database..

only if the field that needs updating is NULL..

can anyone give me a starting point on this? or is there a much better way to do this??

any help would be appreciated

Thanks guys

 

[Siggy19]

If you can give us a few examples of what you want to achieve, we may be able to find a more elegant solution...

Siggy.

 

[lawlerpat]

Yes, w/o examples it would be difficult to speculate.
However, you may consider using a mapping table. A static table with all the 800 combinations built in as 800 records then you can do your update by joining to this table.

Patrick

 

[dvannoy]

OK, heres would be some examples

the field thats needs updating is called P_CODE
in the table there is another field called A_CODE

there are a possible 800 A_CODES to choose from.

also there is a LoginDate of the record..

Now what I need to do is

if A_CODE = 'XYZ' then P_CODE = '7000'

in my UPDATE Statement I would then want it to only look at todays and yesterdays records(based off the logindate) that were NULL..

Thanks

 

[lawlerpat]

Did you consider the mapping table

Update your_table
Set p_code = mt.A_code
From mappingTable mt
Where your_table.LoginDate between dt1 and dt1
and your_table.p_code is null
and mt.A_code = your_table.A_Code

This would require you to have all possibe P_Codes in the mappingTable for all possible A_Codes so your mappingTable would have to have at least two columsn A_code and P_code.

Patrick

 

[dvannoy]

Thanks!!

worked great