InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 03:10:45 GMT20http://www.sqlservercentral.com/Forums/Topic1372642-391-1.aspx[code="sql"]-- try thisselect 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 ACRTINNER JOIN IRBT ON IRBT.MOODYSRATE = ACRT.MOODYSRATECROSS APPLY ( SELECT i.IRBRATING FROM IRBT i WHERE i.ID = IRBT.ID +1) x-- The UPDATE equivalent would look like thisUPDATE ACRT SET Yourcolumn = CASE WHEN ACRT.NEGWATCH = 1 THEN x.IRBRATING ELSE IRBT.IRBRATING ENDFROM ACRTINNER JOIN IRBT ON IRBT.MOODYSRATE = ACRT.MOODYSRATECROSS APPLY ( SELECT i.IRBRATING FROM IRBT i WHERE i.ID = IRBT.ID +1) x[/code]Mon, 15 Oct 2012 10:17:43 GMTChrisM@homehttp://www.sqlservercentral.com/Forums/Topic1372642-391-1.aspxif i did my select like this[code="sql"]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 IRBTon IRBT.MOODYSRATE = ACRT.MOODYSRATE[/code]do i just have to change select with updateif i do it that way i get errorsMon, 15 Oct 2012 07:43:14 GMTronan.healyhttp://www.sqlservercentral.com/Forums/Topic1372642-391-1.aspxok thanks will try thatMon, 15 Oct 2012 04:59:31 GMTronan.healyhttp://www.sqlservercentral.com/Forums/Topic1372642-391-1.aspx[quote][b]ronan.healy (10/15/2012)[/b][hr]update acrt set dbo.ACRT.MIDIRB = dbo.IRBT.IRBRATINGfrom acrt,IRBTwhere dbo.IRBT.id = dbo.IRBT.id +1and NEGWATCH = 1[/quote]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.Mon, 15 Oct 2012 04:55:52 GMTChrisM@homehttp://www.sqlservercentral.com/Forums/Topic1372642-391-1.aspxupdate acrt set dbo.ACRT.MIDIRB = dbo.IRBT.IRBRATINGfrom acrt,IRBTwhere dbo.IRBT.id = dbo.IRBT.id +1and NEGWATCH = 1Mon, 15 Oct 2012 04:27:13 GMTronan.healy