cwarden 74876 (7/30/2014)
Okay, yes I am a SQL newbie 😉 and I am seriously hoping someone can assist with an issue I have been presented with.Issue in a nut shell, I have been tasked with comparing two tables "GLAMF" and "GLPOST" both tables have a common column "ACCTID". GLAMF contains account information where GLPOST contains financial data linking back to the GLAMF table.
I was asked to identify accounts within the GLAMF table that where/are inactive (never used), this was simple enough, to do this I used the following query
SELECT ACCTID from GLAMF
EXCEPT
SELECT ACCTID from GLPOST
This presented me with the unused accounts, now the headache started. I need to somehow, using the output from the previous query, modify/update a column within the GLAMF table to indicate the inactive accounts, the "Activesw", this dictates whether the account is active or inactive (0=inactive, 1=active), if this was just a few records I would have done this manually but I am sitting with just under a million records that need to be amended.
Has anyone got any ideas or able to point me in a direction?
Here is an UPDATE statement. You may want to write a SELECT version and see if it pulls the rows that need to be updated.
UPDATE dbo.GLAMF SET
Activesw = 0
WHERE
ACCTID in (
SELECT ACCTID from GLAMF
EXCEPT
SELECT ACCTID from GLPOST
)