Update Syntax error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Update Syntax error

Author

Topic

Mallen
Starting Member

27 Posts

Posted - 2010-05-16 : 00:13:48

I am trying to run the following UPDATES:
===========
UPDATE Collector_Move_Table
SET Collector_Move_Table.Exception = 0
FROM Collector_Move_Table
WHERE Collector_Move_Table.Queue Like 'Dia%' OR Collector_Move_Table.Queue Like 'Ski%';

UPDATE dbo.Account
SET dbo.Account.QueueID = (SELECT dbo.Bunch.BunchID
FROM dbo.Bunch INNER JOIN dbo.UserData ON dbo.UserData.Value = dbo.Bunch.Description
WHERE dbo.UserData.Value = dbo.Bunch.Description)
WHERE Collector_Move_Table.Exception= 0;
=========
I receive the error in the second UPDATE statement
Msg 4104, Level 16, State 1, Procedure CollectorAccountMoveTEST, Line 41
The multi-part identifier "Collector_Move_Table.Exception" could not be bound.

Why am I getting this error? The first update runs perfectly when I run it by itself. Is there a different way I should write these updates?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-05-16 : 01:42:57

what is the relationship between table Account and Collector_Move_Table ? These 2 tables should join at what columns ?

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-16 : 07:44:47

you have not joined the table Collector_Move_Table in second query so it dont know source table for Exception column

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Mallen
Starting Member

27 Posts

Posted - 2010-05-16 : 10:36:55

hrrm.. there really is no relationship between Account and Collector_Move_Table. I may have to just create some random column in Collector to join them on just so it will work?

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-05-16 : 11:35:33

If there's no relationship between them, how can you use a column in Collector_Move_Table to limit the rows that are updated in Account?

Your update (summarised) says to update rows in Account where a column in Collector_Move_Table has a particular value. How is SQL supposed to work out which rows to update in account?

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources