August 22, 2023 at 2:38 pm
hi,
I have a stage table S, and a final/Target table T with all user details.
I need to update a field in Target Table T , Field is "active" flag for the user, if there is user /record missing in stage table S , the field "active" value will be "N" for inactive user in target table for that record in target.
2. The filed "active" value will be "Y" for active user in target table for that record in target, if the record for the user exists in stage.
My Query:
----ACTVE USERS:
Update Target
SET ACTIVE='Y'
FROM TARGET T
JOIN Stage S on S.user=T.user
-----INACTIVE USERS
Update Target
SET ACTIVE='N'
FROM TARGET T
LEFT JOIN Stage S on S.user=T.user
WHERE S.User is null.
Is there a different way to do this.?
August 22, 2023 at 2:47 pm
This, maybe?
UPDATE T
SET ACTIVE = IIF(S.user IS NULL, 'N', 'Y')
FROM TARGET T
LEFT JOIN Stage S
ON S.user = T.user;
--Edit: wow, square brackets really get dumped on in the code windows!
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
August 22, 2023 at 2:47 pm
Update T --<<-- *must* use T here (the alias and NOT the table name) to be safe
SET ACTIVE=CASE WHEN S.user IS NULL THEN 'N' ELSE 'Y' END
FROM TARGET T
LEFT OUTER JOIN Stage S on S.user=T.user
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
August 22, 2023 at 2:49 pm
dup, removed
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 8, 2023 at 4:59 am
This was removed by the editor as SPAM
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply