Updating a table based on another table.

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

SQL Server Development (2000)

Updating a table based on another table.

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-09-11 : 14:07:20

Chris Pennington writes "Hi there!

I have a dB with 2 related tables, PROJECTS and PROJECTSPHONE with the column PROJECTID in common.

I am trying to update a field calls ROUTINGID within PROJECTSPHONE, based on a column name witin PROJECTS called NAME.

I can select the data using:

select projects.name,
projectsphone.routingid
from projects, projectsphone where projects.projectid = projectsphone.projectid
and projects.name like '%ooh%';

I have tried the following update statement:

update projectsphone
set projectsphone.routingid = '111'
where projectsphone.projectid = projects.projectid
and projects.name like '%ooh%';

However this gives me the following error message:

Server: Msg 107, Level 16, State 3, Line 1
The column prefix 'projects' does not match with a table name or alias name used in the query.

Is this kind of update possible?

I am using SQL server 7.0.

Thanks in advance.

Regards

Chris Pennington"

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2002-09-11 : 14:44:27


update pp 
set pp.routingid = '111' 
from projects p 
join projectsphone pp
on p.projectid = pp.projectid 
where p.name like '%ooh%'

HTH
Jasper Smith

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources