Avatar of 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.
Microsoft SQL Server 2005

Avatar of undefined
Last Comment

8/22/2022 - Mon
David Kroll

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

you have 2 where statements, having one after the brackets isn't correct

merger anything from needed from the second into the first
Ioannis Paraskevopoulos

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

Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy

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?
Ioannis Paraskevopoulos

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question

Thank you all !