Reputation: 546
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
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
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
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