CODEWITHSUNDEEP
sqlsql-servert-sqlsql-update
raine
raine

Reputation: 857

Update all /some / no columns depending on other column values

I was wondering if it's possible to do the following in SQL (I'm using SQL Server 2008+, so vendor-specific stuff might be useful as well for the time being):

I have the following table:

contact (contactid, fullname, fullname_timestamp, age, age_timestamp)

and I'd like to update only those columns whose '*_timestamp' column is less than the given value. for example:

UPDATE contact
SET    fullname = 'john doe' IF fullname_timestamp < 12345,
       age = 30 IF age_timestamp < 12345
WHERE  contactid = 'abcde'

putting these conditions in the WHERE clause would make an "all-or-nothing" scenario (I think?), whereas I'd like to "partially update" a record given these "timestamp" constraints.

Is this possible / am I doing it wrong? Suggestions welcome. Thanks in advance.

Edit: @AaronBertrand raised a few good points and I think they're worth mentioning as additional info to the question. I'll also admit I'm still putting together a solution in my head, and there might be gaps in my thinking.

I'm replicating data contained in an external product. This product has a well defined pipeline of CRUD events. Due to performance reasons, I'm managing these event asynchronously. These events might be queued out-of-order for later processing. I'm using DateTime.UtcNow.Ticks timestamps in one of the non-async pipeline stages of the aforementioned product - so that records are correctly timestamped - and I have means to tell if the data I'm about to update is up-to-date or stale.

-- sync pipeline --

record update 1 - timestamp 1
record update 2 - timestamp 2

-- async queue (can be out-of-order) --
record update 2 - timestamp 2 -> sent to db
record update 1 - timestamp 1 -> sent to db

-- replica db  --
record update 2 goes through, the timestamp is ok
record update 1 does nothing, timestamp shows data is out-of-date

And yes, updates can happen to the same records concurrently. Users could be modifying the same or different fields at the same time. Hope this makes sense. Thanks everyone for the great input.

Upvotes: 0

Views: 779

Answers (1)

John Woo
John Woo

Reputation: 263893

You can use CASE on this to test for specific condition of a certain column,

UPDATE contact
SET    fullname = CASE WHEN fullname_timestamp < 12345 THEN 'john doe' ELSE fullname END,
       age = CASE WHEN age_timestamp < 12345 THEN 30 ELSE age END
WHERE  contactid = 'abcde'

Upvotes: 4

Related Questions