Multiple row update query

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

  • UPDATE GLAMF

    SET Activesw = 0

    WHERE ACCTID NOT IN

    (SELECT ACCTID from GLPOST)


    "If I had been drinking out of that toilet, I might have been killed." -Ace Ventura

  • 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

    )

  • autoexcrement (7/30/2014)


    UPDATE GLAMF

    SET Activesw = 0

    WHERE ACCTID NOT IN

    (SELECT ACCTID from GLPOST)

    :exclamation: This query will work fine is GLPOST is not empty. But if it is empty, the condition will become :

    WHERE ACCTID NOT IN (null)

    which will never be true neither false !). So no line will be updated, instead of all lines :hehe:

    Always be careful with NOT IN. Prefer Lynn Pettis solution : IN

  • Thank you, I was wondering why Lynn wrote it that way! Makes sense.


    "If I had been drinking out of that toilet, I might have been killed." -Ace Ventura

  • Roland C (7/30/2014)


    autoexcrement (7/30/2014)


    UPDATE GLAMF

    SET Activesw = 0

    WHERE ACCTID NOT IN

    (SELECT ACCTID from GLPOST)

    :exclamation: This query will work fine is GLPOST is not empty. But if it is empty, the condition will become :

    WHERE ACCTID NOT IN (null)

    which will never be true neither false !). So no line will be updated, instead of all lines :hehe:

    Not quite.

    NOT IN has the unexpected behaviour you describe when any of the rows in the subquery have the value NULL for the column, not when it's an empty result set. When it's an empty result set, the NOT IN returns all rows, exactly as expected.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Thank you Gila Monster.

    Clearly I made a mistake, sorry :blush:

    I think I remembered it this way because in my mind I saw the logic behind. Now I don't quite understand the logic of the real behaviour. Could you please explain us ?

    Roland

  • http://sqlinthewild.co.za/index.php/2010/02/18/not-exists-vs-not-in/

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Very clear, thank you !

  • Hi

    Although the above mentioned queries will work I have learned not to do Inner select as this creates over head on the tempdb on the server.

    An inner select is sql of this form

    -- Select

    select a.*

    from (

    --Inner Selects

    Select a.<some column> ,b.<some column>

    from <Table A> a

    join <Table B> b on a.1=b.1

    where <some clause>

    )a

    I suggest something like this.

    -- Update records that cant be found in GLPOST

    update a

    set Activesw = 0

    from GLAMF a

    left join GLPOST b with (nolock)

    on a.ACCTID = b.ACCTID

    where b.ACCTID is null

    Hope this add a bit more knowledge.

    --------------------------------------------------------------------------------------------------------------------------------------------------------
    To some it may look best but to others it might be the worst, but then again to some it might be the worst but to others the best.
    http://www.sql-sa.co.za

  • Daniel Matthee (8/1/2014)


    Hi

    Although the above mentioned queries will work I have learned not to do Inner select as this creates over head on the tempdb on the server.

    An inner select is sql of this form

    -- Select

    select a.*

    from (

    --Inner Selects

    Select a.<some column> ,b.<some column>

    from <Table A> a

    join <Table B> b on a.1=b.1

    where <some clause>

    )a

    I suggest something like this.

    -- Update records that cant be found in GLPOST

    update a

    set Activesw = 0

    from GLAMF a

    left join GLPOST b with (nolock)

    on a.ACCTID = b.ACCTID

    where b.ACCTID is null

    Hope this add a bit more knowledge.

    I'd drop the NOLOCK hint as you really don't want a dirty read at this point.

  • Daniel Matthee (8/1/2014)


    I have learned not to do Inner select as this creates over head on the tempdb on the server.

    You have, of course, a reference or an example showing this? Showing that a derived table (what you call an inner select) has overhead on TempDB?

    p.s. why nolock? You do know what that does, don't you?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Hi

    Please help me if I am wrong. But usually when i do an inner select and look at sql monitor the physical reads, or even sp_who2 active the amount of threads that are open (but suspended) are why more than when you do the way i suggest.

    By all means if you disagree, can you explain why you are saying what you are saying? That why we all are learning.

    Kind Regards

    --------------------------------------------------------------------------------------------------------------------------------------------------------
    To some it may look best but to others it might be the worst, but then again to some it might be the worst but to others the best.
    http://www.sql-sa.co.za

  • I'm saying that's a pretty extreme statement to make, claiming that all subqueries and derived tables cause TempDB overhead (which, btw, would not show as physical reads, they'd show as logical reads from a worktable, unless there's so little memory available that SQL's having to force TempDB pages out of cache). Extraordinary claims, such as that, require either a large number of reproducible examples or a reputable reference, or they're like someone claiming that the sky is purple. Especially when it's trivial to show that the example you gave does not have any TempDB overhead and, in fact, has an identical execution plan as an equivalent query without the derived table, and that NOT IN with subqueries has a slightly lower cost and the same reads as the equivalent query with LEFT OUTER JOIN ... IS NULL (see the link I posted earlier)

    Are the suspended sessions you mentioned related in any way to the running query? Are you seeing additional IO on the TempDB files? Additional allocations in TempDB? Waits indicative of IO with resources related to TempDB? What table are the physical reads being done against? Are you clearing the cache between executions (which would cause reads to be physical)?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • GilaMonster (8/2/2014)


    I'm saying that's a pretty extreme statement to make, claiming that all subqueries and derived tables cause TempDB overhead (which, btw, would not show as physical reads, they'd show as logical reads from a worktable, unless there's so little memory available that SQL's having to force TempDB pages out of cache). Extraordinary claims, such as that, require either a large number of reproducible examples or a reputable reference, or they're like someone claiming that the sky is purple. Especially when it's trivial to show that the example you gave does not have any TempDB overhead and, in fact, has an identical execution plan as an equivalent query without the derived table, and that NOT IN with subqueries has a slightly lower cost and the same reads as the equivalent query with LEFT OUTER JOIN ... IS NULL (see the link I posted earlier)

    Are the suspended sessions you mentioned related in any way to the running query? Are you seeing additional IO on the TempDB files? Additional allocations in TempDB? Waits indicative of IO with resources related to TempDB? What table are the physical reads being done against? Are you clearing the cache between executions (which would cause reads to be physical)?

    Coolio, I will then hereby take back that statement. Sorry for that.

    Can you please explain then when using an inner select statement (to minimize the sample )on huge table. (+- 20 columns, 400mil rows) the query runs WAY longer than actually putting the minimized sampel into and temp table and then reading from it.

    I am still new to SQL so by all means I am not an expert and only saying what I experience.

    Kind Regards

    --------------------------------------------------------------------------------------------------------------------------------------------------------
    To some it may look best but to others it might be the worst, but then again to some it might be the worst but to others the best.
    http://www.sql-sa.co.za

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply