CODEWITHSUNDEEP
sqlms-accessvbasql-update
st87_top
st87_top

Reputation: 57

sql update table - statement not working

I have the below statement in my VBA:

DoCmd.RunSQL "UPDATE customer SET Status = 'Premier' WHERE customer_id = 41308408 AND location IN ('London','New York') AND Status = ''"

which is meant to update a table called "customer". I can see in my table there are about 20 entries where the customer_id is 41308408 (i.e. if i filter the column for that value), with the location being either London or New York for each entry. The Status column is blank for each of these.

I execute the above code, and it compiles OK, but it says "You are about to update 0 rows". I would be expecting that to be 20, as per above. Any ideas?

Upvotes: 2

Views: 12145

Answers (3)

Swati Joshi
Swati Joshi

Reputation: 71

UPDATE customer SET Status = 'Premier' WHERE customer_id = 41308408 AND location IN ('London','New York') AND ((Status is null) or length(trim(Status)) = 0)

Upvotes: 0

Mureinik
Mureinik

Reputation: 310983

The blank is probably a null in the database. Nulls are values - they are the lack thereof. You can't query them with the = operator, you need to treat them explicitly with the is operator:

DoCmd.RunSQL "UPDATE customer SET Status = 'Premier' WHERE customer_id = 41308408 AND location IN ('London','New York') AND Status IS NULL"

Upvotes: 0

Robert
Robert

Reputation: 25753

Maybe status isn't blank, maybe it's null? Try below solution:

UPDATE customer 
SET Status = 'Premier' 
WHERE customer_id = 41308408 
AND location IN ('London','New York') 
AND Status is null

Upvotes: 1

Related Questions