How can I perform an UPDATE if EXISTS based on existing data in SQL Server

Question

I am pulling data from an application (via Python) and writing that data into a SQL Server database. My initial data loads, however I will need to UPDATE that data based on subsequent API retrievals from my Python script. While I am successfully able to update all existing rows in the my database I only want to update the values that are different. To visualize:

dev_name    userID
========    ======
abc          def
ghi          jkl
mno          NULL

When Python pulls new data and sees a different uid value for row 'mno' for example, the UPDATE statement should only update that row. Non NULL columns could be updated as well. I thought the following should have worked, however it doesn't.

rslt = cursor.execute(IF EXISTS(SELECT device_name from Devices where device_name = ?) UPDATE Devices SET userID = (?) where device_name = ? and userID <> ?", dev, uid, dev, uid)

To clarify, the parameters of dev and uid refer to the Python variables that contain the data from the API call.

Any clarification or explanation is greatly appreciated.

"IF EXISTS" is not valid SQL and it's pointless anyways. UPDATE will only update rows that match the WHERE condition, if they exist. if none exist then nothing gets updated — Kurt
– Kurt, Commented Jan 20, 2023 at 19:08
note in the case where userID is NULL, comparing it to a value will always be false, so you should use and (userID <> ? or userID is null) — Kurt
– Kurt, Commented Jan 20, 2023 at 19:11
The only exception would be a LEFT JOIN, along with an "is null" operator to validate if the value exists. — swolfe2
– swolfe2, Commented Jan 20, 2023 at 19:11

FlexYourData · Accepted Answer · 2023-01-24 22:52:21Z

1

In SQL, the WHERE predicate is how you determine existence of a record.

Presumably you want to set the userID to the current owner of the device. Supposing the new userID is 'pqr', then:

UPDATE Devices 
SET userID = 'pqr'
where device_name = 'mno'
and userID <> 'pqr';

So for this specific example:

query = """UPDATE Devices 
           SET userID = ?
           where device_name = ?
           and userID <> ? """
args = ('pqr', 'mno', 'pqr')
cursor.execute(query, args)

Or in your case:

query = """UPDATE Devices 
           SET userID = ?
           where device_name = ?
           and userID <> ? """
args = (uid, dev, uid)
cursor.execute(query, args)

Reference

EDIT to handle NULL values:

query = """UPDATE Devices 
           SET userID = ? 
           WHERE device_name = ? 
           AND COALESCE(userID,'NULL') <> COALESCE(?,'NULL'))) """ 
args = (uid,dev,uid)

edited Jan 24, 2023 at 22:52

answered Jan 20, 2023 at 19:24

FlexYourData

2,4941 gold badge14 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Chad Over a year ago

Thanks. That works great. To echo @Kurt I had to add the 'userID <> ? or userID is null' as that column allows for NULL.

FlexYourData Over a year ago

You can also use and coalesce(userID,'') <> ?. If UserID is null, COALESCE to empty string will put an empty string in the comparison, which doesn't have the same problem as NULL.

Chad Over a year ago

Can someone also explain why the query won't update to be NULL? Using the above query, if there is currently data in a row and needs to be updated to either a 0 or NULL, that row is bypassed. I would think the <> should have accounted for that?

FlexYourData Over a year ago

You can't compare with NULL using the not equals operator (or the equals operator). You need to COALESCE both sides of the (in)equality to an empty string in order to draw a valid comparison. Like this: WHERE COALESCE(userID,'') <> COALESCE( ?, '') Alternatively, you can handle nulls in this way: WHERE COALESCE(userID,'') <> ? OR (? IS NULL AND userID IS NOT NULL)

Chad Over a year ago

I have been trying iterations of this all day and cannot seem to get it correct. I get the basic concept of 'coalesce', however my syntax is off and not updating both populated and NULL values. I have the following; '''query = UPDATE Devices SET userID = ? where device_name = ? and userID <> ? or device_name = ? and COALESCE(userID,'NULL') <> ?""" args = (uid,dev,uid,dev,uid)''' Can you advise where things are falling apart? I am struggling to understand the full logic. Thank you.

|

Collectives™ on Stack Overflow

How can I perform an UPDATE if EXISTS based on existing data in SQL Server

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related