Geek Answers Handbook

Multiple Conditions MS SQL Update Table

I read this site for answers for a while and now asked my first question!

I am using SQL Server

I have two tables: ABC and ABC_Temp .

Before switching to ABC, the contents are inserted into ABC_Temp . The ABC and ABC_Temp tables contain the same columns, except that ABC_Temp has an additional column called LastUpdatedDate that contains the date of the last update. Since ABC_Temp can have more than 1 single record, it has a composite key for the item number and the last updated date.

Columns: ItemNo | Price | Qty and ABC_Temp has an additional column: LastUpdatedDate

I want to create an operator that follows the following conditions:

  • Make sure that each of the ABC attributes is different from the value of ABC_Temp for records with the same key, if so, do an update (even if only one attribute is different, all other attributes can also be updated)
  • Only update those that need changes, if the record is the same, then it will not be updated.
  • Since an item can have more than one entry in ABC_Temp , I want the last updated to be updated to ABC

I am currently using 2005 (I think it’s not working right now).

This will be stored in the procedure and called inside the scheduled VBscript task. Therefore, I believe that this is one thing. Also, I am not trying to synchronize the two tables, since the contents of ABC_Temp will contain only new entries attached to the text file via BCP. For context, this will be used along with the insert stored in proc, which checks for records.

+4
source share
2 answers
UPDATE ABC SET price = T1.price, qty = T1.qty FROM ABC INNER JOIN ABC_Temp T1 ON T1.item_no = ABC.item_no LEFT OUTER JOIN ABC_Temp T2 ON T2.item_no = T1.item_no AND T2.last_updated_date > T1.last_updated_date WHERE T2.item_no IS NULL AND ( T1.price <> ABC.price OR T1.qty <> ABC.qty )

If NULL values ​​are possible in the price or qty columns, you will need to consider this. In this case, I would probably change the inequality operators like this:

 COALESCE(T1.price, -1) <> COALESCE(ABC.price, -1)

This assumes that -1 is not a valid value in the data, so you don't have to worry about it really appearing there.

Also, is ABC_Temp really a temporary table that is just loaded long enough to get values ​​in ABC? If not, then you save duplicate data in several places, which is a bad idea. The first problem is that now you need these update scripts. There are other problems that may arise, for example, inconsistencies in the data, etc.

+2
source

You can use cross apply to find the last line in ABC_Temp using the same key. Use the where clause to filter strings without distinction:

 update abc set col1 = latest.col1 , col2 = latest.col2 , col3 = latest.col3 from ABC abc cross apply ( select top 1 * from ABC_Temp tmp where abc.key = tmp.key order by tmp.LastUpdatedDate desc ) latest where abc.col1 <> latest.col1 or (abc.col2 <> latest.col2 or (abc.col1 is null and latest.col2 is not null) or (abc.col1 is not null and latest.col2 is null)) or abc.col3 <> latest.col3

In this example, only col2 is NULL. Since null <> 1 not true, you need to check for differences related to null using the is null syntax.

+1
source

More articles:


All Articles