URGENT HELP PLEASE

  • I have this select statement:

    select a.active from sys_scd_profile a

    inner join sys_scd_profile_activation b on (a.id = b.profile_id)

    where a.active = 1 and b.date_deactivated is not NULL and b.date_reactivated is NULL and b.date_deactivated < '7 aug 2012'

    I need to update all a.active to = 0 where it exists in the select statement.

    Please help. very urgent

  • Do you mean like this?

    UPDATE a

    SET a.active = 0

    FROM sys_scd_profile a

    INNER JOIN sys_scd_profile_activation b

    ON a.id = b.profile_id

    WHERE a.active = 1 and b.date_deactivated is not NULL and

    b.date_reactivated is NULL and

    b.date_deactivated < '7 aug 2012'


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • Awesome!!! Result i needed. Thanks heaps 🙂

  • Note that "b.date_deactivated is not NULL" is superfluous. If b.date_deactivated is NULL then the result of "b.date_deactivated < '7 aug 2012'" will also be NULL. So, in order for the second statement to be TRUE, the first statement must be true.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • CELKO (8/7/2012)


    It looks like you use assembly language bit flays

    Until SQL support Boolean datatypes, bit flags are the next best thing.

    , but you did not bother to post any DDL so we have no idea what help you need. Just follow basic Netiquette, please.

    Perhaps you should try reading the thread instead of pushing your personal agenda. The question has already been answered, so obviously there was enough information provided

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

Viewing 5 posts - 1 through 4 (of 4 total)

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