CODEWITHSUNDEEP
sqlsql-serversql-update
zentenk
zentenk

Reputation: 2815

MS SQL update table with multiple conditions

Been reading this site for answers for quite a while and now asking my first question!

I'm using SQL Server

I have two tables, ABC and ABC_Temp.

The contents are inserted into the ABC_Temp first before making its way to ABC. Table ABC and ABC_Temp have the same columns, except that ABC_Temp has an extra column called LastUpdatedDate, which contains the date of the last update. Because ABC_Temp can have more than 1 of the same record, it has a composite key of the item number and the last updated date.

The columns are: ItemNo | Price | Qty and ABC_Temp has an extra column: LastUpdatedDate

I want to create a statement that follows the following conditions:

  1. Check if each of the attributes of ABC differ from the value of ABC_Temp for records with the same key, if so then do the update (Even if only one attribute is different, all other attributes can be updated as well)
  2. Only update those that need changes, if the record is the same, then it would not update.
  3. Since an item can have more than one record in ABC_Temp I only want the latest updated one to be updated to ABC

I am currently using 2005 (I think, not at work at the moment).

This will be in a stored procedure and is called inside the VBscript scheduled task. So I believe it is a once time thing. Also I'm not trying to sync the two tables, as the contents of ABC_Temp would only contain new records bulk inserted from a text file through BCP. For the sake of context, this will be used with in conjunction with an insert stored proc that checks if records exist.

Upvotes: 4

Views: 18719

Answers (2)

Tom H
Tom H

Reputation: 47402

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 then you will need to account for that. In this case I would probably change the inequality statements to look 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 actually appearing there.

Also, is ABC_Temp really a temporary table that's just loaded long enough to get the values into ABC? If not then you are storing duplicate data in multiple places, which is a bad idea. The first problem is that now you need these kinds of update scenarios. There are other issues that you might run into, such as inconsistencies in the data, etc.

Upvotes: 2

Andomar
Andomar

Reputation: 238296

You could use cross apply to seek the last row in ABC_Temp with the same key. Use a where clause to filter out rows with no differences:

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 the example, only col2 is nullable. Since null <> 1 is not true, you have to check differences involving null using the is null syntax.

Upvotes: 1

Related Questions