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 2005 Forums

Transact-SQL (2005)

UPDATE help

Author

Topic

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2010-09-15 : 12:23:40

Hi,

I have this


UPDATE Main A
SET A.Source = 
(SELECT DISTINCT B.Source FROM Figures B
WHERE (A.VehicleRef = B.VehicleRef) AND (B.Source = 'SPC'))

I get Incorrect syntax near 'A'. Can anyone help?

Thanks

X002548
Not Just a Number

15586 Posts

Posted - 2010-09-15 : 12:30:57

[code]
UPDATE A
SET Source = B.Source
FROM Main A
INNER JOIN Figures B
ON A.VehicleRef = B.VehicleRef
WHERE B.Source = 'SPC'
[/code]

Not Tested DDL and sample data would be helpful

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-09-15 : 12:32:24

quote:
Originally posted by X002548

UPDATE ~~Main~~ A
SET A.Source = B.Source
FROM Main A
INNER JOIN Figures B
ON A.VehicleRef = B.VehicleRef
WHERE B.Source = 'SPC'

Not Tested DDL and sample data would be helpful

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2010-09-15 : 12:37:34

Hi,

Main
------
vehicleref source
1 NULL
2 NULL
3 NULL

Figures
-------
vehicleref source
1 AAA
1 BBB
1 AAA
2 AAA
2 BBB
2 SPC
3 SPC
3 SPC
3 SPC

I want to set main.source to 'SPC' if 1 or more figures rows have 'SPC' as the source

In this case 2 and 3 should be set to 'SPC'

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources