davidjwest
davidjwest

Reputation: 546

Only Update Table if Field is NULL

I have this query which is working OK

    UPDATE Master 
    SET Closed = Date(), 
        Status = 'Closed', 
        [Closure Reason] = 'Not on Extract'
    WHERE NOT EXISTS ( SELECT 1
                       FROM Extract
                       WHERE Master.Case = Extract.Case 
                     );

I need to amend it to add another criteria, ie ONLY do the update if the value of Master.[Closure Reason] is NULL

But this gives a syntax error:

UPDATE Master SET Closed = Date(), Status = 'Closed', [Closure Reason] = 'Not on Extract'
WHERE [Closure Reason] = "" AND
WHERE NOT EXISTS (SELECT 1
                  FROM Extract
                  WHERE Master.Case = Extract.Case
                 );

I've tried using IS NULL rather than = "" to no avail.

What is wrong with it please?

Upvotes: 1

Views: 100

Answers (3)

HoneyBadger
HoneyBadger

Reputation: 15150

UPDATE Master SET Closed = Date(), Status = 'Closed', [Closure Reason] = 'Not on Extract'
WHERE [Closure Reason] IS NULL 
AND NOT EXISTS (SELECT 1
                  FROM Extract
                  WHERE Master.Case = Extract.Case
                 );

You can't add multiple WHERE clauses, you need to use the AND. If your requirement is "ONLY do the update if the value of Master.[Closure Reason] is NULL", then check for NULL, not for an empty string.

Upvotes: 3

Rahul
Rahul

Reputation: 77896

You need to add a AND conditional operator instead of having two WHERE clauses like

WHERE NOT EXISTS (SELECT 1
                  FROM Extract
                  WHERE Master.Case = Extract.Case
                 )
AND [Closure Reason] IS NULL;

Upvotes: 1

Utsav
Utsav

Reputation: 8103

You need to remove the second where before not exists.

UPDATE Master SET Closed = Date(), Status = 'Closed', [Closure Reason] = 'Not on Extract'
WHERE [Closure Reason] = "" AND
 NOT EXISTS (SELECT 1
                  FROM Extract
                  WHERE Master.Case = Extract.Case
                 );

Upvotes: 1

Related Questions