ronan.healy (10/9/2012)
hiout of interest is it possible to do it this way
update ACRT set MIDIRB = min(IRBT2.IRBRATING)from ACRT,IRBT,IRBT2 where ACRT.MIDIRB = IRBT.IRBRATING
and IRBT2.IRBRATING > IRBT.IRBRATING
and if so what do i have to do to get this error out of it
Msg 208, Level 16, State 1, Line 1
Invalid object name 'IRBT2'.
IRBT2 is a table in database
No - there's an aggregate operator but no GROUP BY clause.
-- test this
SELECT
ACRT.*,
x.MIDIRB
FROM ACRT
INNER JOIN IRBT
ON IRBT.IRBRATING = ACRT.MIDIRB
CROSS APPLY (
SELECT MIDIRB = MIN(IRBT2.IRBRATING)
FROM IRBT2
WHERE IRBT2.IRBRATING > IRBT.IRBRATING
) x
-- if it correctly displays the rows to be updated and the correct value to update to,
-- then convert the SELECT to an UPDATE:
UPDATE ACRT SET MIDIRB = x.MIDIRB
FROM ACRT
INNER JOIN IRBT
ON IRBT.IRBRATING = ACRT.MIDIRB
CROSS APPLY (
SELECT MIDIRB = MIN(IRBT2.IRBRATING)
FROM IRBT2
WHERE IRBT2.IRBRATING > IRBT.IRBRATING
) 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]