SQL Update Help - 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 2000 Forums

Transact-SQL (2000)

SQL Update Help

Author

Topic

melcraig
Starting Member

39 Posts

Posted - 2005-12-13 : 13:43:35

I am trying to learn SQL on my own. Can someone please tell me what I am doing wrong in this update query.
I have two tables I am joining on EmployeeID then If the LastName starts with the letters a-g and the DateAcctToBeDisabled = 12/31 I want to change the date to 10/31. Please, any help will be much appreciated.
Thanks, Melinda

SELECT dbo.tnpEmployee.LastName, dbo.tnpEmployee.EmployeeID, dbo.tnpNPGSAcct.EmployeeID,dbo.tnpNPGSAcct.DateAcctToBeDisabled

FROM dbo.tnpNPGSAcct INNER JOIN
dbo.tnpEmployee ON dbo.tnpNPGSAcct.EmployeeID = dbo.tnpEmployee.EmployeeID

UPDATE dbo.tnpNPGSAcct

SET DateAcctToBeDisabled = '3000-10-31 00:00:000'

WHERE dbo.tnpEmployee.LastName LIKE '[a-g]%' AND dbo.tnpNPGSAcct.DateAcctToBeDisabled ='2008-12-31 00:00:000'

This is the error I receive:
Server: Msg 107, Level 16, State 3, Line 5
The column prefix 'dbo.tnpEmployee' does not match with a table name or alias name used in the query.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-12-13 : 13:53:40

When you update a table with WHERE criteria that references another table you need a FROM clause. Here's how I would write your 2 statements.


select	e.LastName
	,e.EmployeeID
	,a.EmployeeID
	,a.DateAcctToBeDisabled 
from	dbo.tnpNPGSAcct as a
inner join dbo.tnpEmployee as e
	     on a.EmployeeID = e.EmployeeID 

update a SET 
	a.DateAcctToBeDisabled = '3000-10-31 00:00:000'
from	dbo.tnpNPGSAcct as a
inner join dbo.tnpEmployee as e
	    on a.EmployeeID = e.EmployeeID 
where	e.LastName LIKE '[a-g]%' 
and	a.DateAcctToBeDisabled ='2008-12-31 00:00:000'

EDIT:
the table aliases and columnar formatting make the statements easier to read.

Be One with the Optimizer
TG

melcraig
Starting Member

39 Posts

Posted - 2005-12-13 : 14:11:32

TG
Thanks so much for your help. I'm having difficulties with alias'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources