TSQL Question

  • 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.?

     

     

     

     

     

    • This topic was modified 10 months ago by  komal145.
  • 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!

    • This reply was modified 10 months ago by  Phil Parkin.
    • This reply was modified 10 months ago by  Phil Parkin.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • 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) A socialist is someone who will give you the shirt off *someone else's* back.

  • dup, removed

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • 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