Update based on multiple conditions

[livezone]

Hi,
I am using the following 4 statements to update the same record based on different conditions. Is it possible to write all 4 update statement into 1 update statement.

Update OrderHeader Set Status = '3' Where OrderID = @OrderID
Update oh
Set TruckNumber = @TruckNumber
Where OrderID = @OrderID and TruckNumber is not null

Update oh
Set DeliveryDate = @DeliveryDate
Where OrderID = @OrderID and @DeliveryDate is not null

Update oh
Set DCTransfer = @DCTransfer
Where OrderID = @OrderID and @DCTransfer is not null

Thanks
Shafiq

 

[amrita418]

Try this:

Update  oh
Set     Status = '3'
Set 	TruckNumber = case when TruckNumber is null then @TruckNumber else TruckNumber end,
Set 	DeliveryDate = case when DeliveryDate is null then @DeliveryDate else DeliveryDate end,
Set 	DCTransfer = case when DeliveryDate is null then @DCTransfer else DCTransfer end
Where 	OrderID = @OrderID

You can get rid of the case stmt if for every orderid, the values for TruckNumber, DeliveryDate, DCTransfer are always null.

Regards,
AA

 

[vongrunt]

Regarding original post... "is not null" looks somewhat odd.

One note: if there are some triggers on OrderHeader table, better check everything twice.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)