April 29, 2019 at 3:14 pm
Thanks in advance.. I'm trying to update a field "expired" for a person in table "BillingThirdNotice" setting it to -1 if that person has "certificationExpireDate" before today's date in the table "Certs". I have a start on the code and there may be a better way... any ideas?:
UPDATE GCDF_DB..BillingThirdNotice B
SET B.expired = -1
INNER JOIN GCDF_DB..Certs C
ON B.PeopleID = C.PeopleID
WHERE
C.certificationExpireDate < Now()
April 29, 2019 at 3:35 pm
Are you using SQL Server? There's no such thing as "Now()", as far as I know.
Something like this may work for you:
UPDATE B
SET expired = -1
FROM GCDF_DB..BillingThirdNotice B
INNER JOIN GCDF_DB..Certs C
ON B.PeopleID = C.PeopleID
WHERE C.certificationExpireDate < GETDATE()
John
April 29, 2019 at 3:48 pm
Are you using SQL Server? There's no such thing as "Now()", as far as I know.
A Google suggests that NOW() is a MySQL function. Hopefully the OP has just mixed up their syntax for the different RDBMS; if not then I would expect that query to work on MySQL (apart from that GETDATE() would need to be changed back to NOW()). Of course if the OP is using MySQL, they're posting on the wrong website. 🙂
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
April 29, 2019 at 3:50 pm
I'm using SQL Server but got mixed up as the database has an Access frontend which uses Now().
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply