Simple update problem - 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

General SQL Server Forums

New to SQL Server Programming

Simple update problem

Author

Topic

mike009
Starting Member

15 Posts

Posted - 2009-05-07 : 11:29:23

Hi all

MS server 2005

I want to update a table from a view

i want to update some columns in the project table from the qry_temp view but i get strange error

table project :

PROJECT_ID UPDATE_TIME UPDATE_USER Actuals DATE_YEAR
----------- ----------- ----------- ------- -----------
1 05/05/2009 TIOO69 50€ 2009
2 05/05/2009 TIOO69 10€ 2009
3 05/05/2009 TIOO69 55€ 2009
4 05/05/2009 TIOO69 58€ 2009
5 05/05/2009 TIOO69 57€ 2009

View qry_TEMP

ProjectID Actuals
----------- -------
1 100€
3 510€
5 58€



Update tbl_project left JOIN qry_TEMP

ON (tbl_project.PROJECT_ID = qry_TEMP.ProjectID)

set UPDATE_TIME = getdate(),

UPDATE_USER = 'FFB539',

ACTUALS = qry_TEMP.Actuals

WHERE (tbl_project.DATE_YEAR = 2009)

The Error Message is



Msg 156, Level 15, State 1, Line 1

Incorrect syntax near the keyword 'left'.

Thanks in advance

Mike

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-05-07 : 11:51:56

Your query has several issues. But, maybe this will work?

UPDATE 
	Project
SET
	UPDATE_TIME = getdate(),
	UPDATE_USER = 'FFB539',
	ACTUALS = qry_TEMP.Actuals
FROM
	tbl_project as Project
left JOIN 
	qry_TEMP
	ON Project.PROJECT_ID = qry_TEMP.ProjectID
WHERE 
	Project.DATE_YEAR = 2009

EDIT: confused columns to be updated with the join condition

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-05-07 : 11:55:21

Update tbl_project

set UPDATE_TIME = getdate(),
UPDATE_USER = 'FFB539',
ACTUALS = qry_TEMP.Actuals

from tbl_project

left JOIN qry_TEMP

ON (tbl_project.PROJECT_ID = qry_TEMP.ProjectID)

WHERE (tbl_project.DATE_YEAR = 2009)

Jim

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources