Link to home
Start Free TrialLog in
Avatar of minglelinch
minglelinch

asked on

Query Question

I have the following query -

update dbo.AllData$ t1
set t1.Con = (
select t2.Con
from dbo.AllData$ t2
where t2.Log<121 and t2.Loc=t1.Loc
)
where t1.Loc=t2.Loc
t1.Log > 120

But I got error:

Incorrect syntax near 't1'.
Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'where'.

I cannot locate where the problem is. I appreciate any help.
Avatar of David Kroll
David Kroll
Flag of United States of America image

update dbo.AllData$
set Con = t2.Con
from dbo.AllData$ t1
inner join dbo.AllData$ t2 on t2.Log<121 and t2.loc=t1.loc
where t1.Log > 120
Avatar of PortletPaul
you have 2 where statements, having one after the brackets isn't correct

merger anything from needed from the second into the first
You may not have allias to the table after tha UPDATE statement. I suggest you run the following and if you are ok with the result proceed to the UPDATE.

SELECT t1.con,t2.con
FROM dbo.AllData$ t1
          JOIN dbo.AllData$ t2
                ON t2.Loc = t1.Loc
WHERE t2.Log<121
           AND t1.Log>120

Open in new window


UPDATE dbo.AllData$ 
SET Con = t2.Con
FROM dbo.AllData$ t1
          JOIN dbo.AllData$ t2
                ON t2.Loc = t1.Loc
WHERE t2.Log<121
           AND t1.Log>120

Open in new window


Giannis
Avatar of minglelinch
minglelinch

ASKER

Thanks for all comments.

Giannis, I ran your select block code, it is working fineand that's what want. But when I ran the Update code, I got error:
Msg 8154, Level 16, State 1, Line 1
The table 'dbo.AllData$' is ambiguous.

Any additional I ignored?
ASKER CERTIFIED SOLUTION
Avatar of Ioannis Paraskevopoulos
Ioannis Paraskevopoulos
Flag of Greece image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thank you all !