do this update even make sense

  • update acrt

    set dbo.ACRT.MIDIRB = dbo.IRBT.IRBRATING

    from acrt,IRBT

    where dbo.IRBT.id = dbo.IRBT.id +1

    and NEGWATCH = 1

  • ronan.healy (10/15/2012)


    update acrt

    set dbo.ACRT.MIDIRB = dbo.IRBT.IRBRATING

    from acrt,IRBT

    where dbo.IRBT.id = dbo.IRBT.id +1

    and NEGWATCH = 1

    It won't update any rows because dbo.IRBT.id will never equal dbo.IRBT.id +1.

    Write it as a SELECT first, so that it returns the correct ACRT rows to update with the correct values from IRBT. It's easy to check and simple to convert into an UPDATE.


    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].


    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

  • ok thanks will try that

  • if i did my select like this

    select MIDIRB,

    IRBT.ID as IRBT_ID,

    IRBT.IRBRATING as IRBT_IRBRATING,

    ACRT.NEGWATCH as ACRT_NEGWATCH,

    case when ACRT.NEGWATCH = 1 then (SELECT IRBT.IRBRATING FROM IRBT I WHERE I.ID = (IRBT.ID +1)) ELSE IRBT.IRBRATING end,

    *

    FROM ACRT

    inner join IRBT

    on IRBT.MOODYSRATE = ACRT.MOODYSRATE

    do i just have to change select with update

    if i do it that way i get errors

  • -- try this

    select

    MIDIRB,

    IRBT.ID as IRBT_ID,

    IRBT.IRBRATING as IRBT_IRBRATING,

    ACRT.NEGWATCH as ACRT_NEGWATCH,

    Newcolumn = case when ACRT.NEGWATCH = 1 then x.IRBRATING ELSE IRBT.IRBRATING end,

    *

    FROM ACRT

    INNER JOIN IRBT

    ON IRBT.MOODYSRATE = ACRT.MOODYSRATE

    CROSS APPLY (

    SELECT i.IRBRATING

    FROM IRBT i

    WHERE i.ID = IRBT.ID +1

    ) x

    -- The UPDATE equivalent would look like this

    UPDATE ACRT

    SET Yourcolumn = CASE

    WHEN ACRT.NEGWATCH = 1 THEN x.IRBRATING

    ELSE IRBT.IRBRATING END

    FROM ACRT

    INNER JOIN IRBT

    ON IRBT.MOODYSRATE = ACRT.MOODYSRATE

    CROSS APPLY (

    SELECT i.IRBRATING

    FROM IRBT i

    WHERE i.ID = IRBT.ID +1

    ) x


    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].


    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

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

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