Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

do this update even make sense Expand / Collapse
Author
Message
Posted Monday, October 15, 2012 4:27 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, June 27, 2014 6:30 AM
Points: 71, Visits: 132
update acrt
set dbo.ACRT.MIDIRB = dbo.IRBT.IRBRATING

from acrt,IRBT
where dbo.IRBT.id = dbo.IRBT.id +1

and NEGWATCH = 1
Post #1372642
Posted Monday, October 15, 2012 4:55 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 11:23 PM
Points: 1,071, Visits: 6,322
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.



Low-hanging fruit picker and defender of the moggies





For better assistance in answering your questions, please read this.




Understanding and using APPLY, (I) and (II) Paul White

Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Post #1372649
Posted Monday, October 15, 2012 4:59 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, June 27, 2014 6:30 AM
Points: 71, Visits: 132
ok thanks will try that
Post #1372650
Posted Monday, October 15, 2012 7:43 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, June 27, 2014 6:30 AM
Points: 71, Visits: 132
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
Post #1372728
Posted Monday, October 15, 2012 10:17 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 11:23 PM
Points: 1,071, Visits: 6,322
-- 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




Low-hanging fruit picker and defender of the moggies





For better assistance in answering your questions, please read this.




Understanding and using APPLY, (I) and (II) Paul White

Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Post #1372805
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse