October 15, 2012 at 4:27 am
update acrt
set dbo.ACRT.MIDIRB = dbo.IRBT.IRBRATING
from acrt,IRBT
where dbo.IRBT.id = dbo.IRBT.id +1
and NEGWATCH = 1
October 15, 2012 at 4:55 am
ronan.healy (10/15/2012)
update acrtset 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.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
October 15, 2012 at 4:59 am
ok thanks will try that
October 15, 2012 at 7:43 am
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
October 15, 2012 at 10:17 am
-- 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
For better assistance in answering your questions, please read this[/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