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 2008 Forums
 Transact-SQL (2008)
 Update statement with subquery syntax error

Author  Topic 

duanecwilson
Constraint Violating Yak Guru

273 Posts
Posted - 2010-04-13 : 12:37:03
I just can't seem to get this. The sub query works by itself:
Update [SMS-Appdata]
Set [AppName] = (
Select Distinct C.[AppName] from [CommonApp] C
inner join [SMS-Appdata] S ON C.AppID = S.[AppID] 
Where S.[AppName] IS NOT NULL And C.[AppID] <> 60) S


I get the error:
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'S'.

I would appreciate any help here. I have tried many different variations.

Duane

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts
Posted - 2010-04-13 : 13:11:22
I am not much clear on your requirement. I framed the below two update stmt. Check whether it help you ..
If both the queries are giving you reqd output then i will suggest that you go with the second one.

Update [SMS-Appdata]
Set [AppName] = SubTab.AppName
From [SMS-Appdata] MTab inner join
( Select Distinct AppID, C.[AppName] as AppName from [CommonApp] C
inner join [SMS-Appdata] S ON C.AppID = S.[AppID]
Where S.[AppName] IS NOT NULL And C.[AppID] <> 60) as SubTab
On SubTab.AppID = Mt.AppID


Update [SMS-Appdata]
Set [AppName] = C.[AppName]
From [SMS-Appdata] inner join [CommonApp] C on
[SMS-Appdata].[AppID] = C.AppID
AND C.[AppID] <> 60

If you are not getting the reqd output then i suggest that you post some sample data to get more detailed view of your requirement.

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.
Go to Top of Page

Asken
Starting Member

38 Posts
Posted - 2010-04-14 : 01:24:00
Remove the very last 'S' and it should work. Please also note that the subquery must only return 1 value...

Reporting & Analysis Specialist
Helping others helps me get better...
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2010-04-14 : 01:39:54
quote:
Originally posted by Asken

Remove the very last 'S' and it should work. Please also note that the subquery must only return 1 value...

Reporting & Analysis Specialist
Helping others helps me get better...

It doesnt have any relation specified with subquery so it will cause all records to be updated with same value for [AppName] in [SMS-Appdata]


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

Ifor
Aged Yak Warrior

700 Posts
Posted - 2010-04-14 : 06:39:53
In SQL2008, MERGE is safer than an UPDATE FROM with a JOIN.
Try something like:

;WITH CTE
AS
(
	SELECT C1.AppID, C1.AppName
	FROM CommonApp C1
	WHERE C1.AppID <> 60
		AND EXISTS
		(
			SELECT *
			FROM [SMS-Appdata] S1
			WHERE S1.AppName IS NOT NULL
				AND S1.AppID = C1.AppID
		)
)
MERGE INTO [SMS-Appdata] S
USING CTE C
	ON S.AppID = C.AppID
WHEN MATCHED
THEN UPDATE
	SET AppName = C.AppName
Go to Top of Page

duanecwilson
Constraint Violating Yak Guru

273 Posts
Posted - 2010-04-15 : 17:39:19
Thanks for all the input. I just noticed it now as I had been distracted. I tested all these (parsing only) and they parsed correctly (Ifor's needed a semi-colon after the merge - no big deal). What I actually ended up with (with some help) was:
UPDATE S
SET [AppName] = X.[AppName] 
FROM [SMS-Appdata] S
INNER JOIN 
(
SELECT MIN(C.[AppName]) AS AppName, C.AppID  
FROM [CommonApp] C GROUP BY C.AppID
) X ON X.AppID = S.[AppID] 
WHERE S.[AppName] IS NOT NULL

It worked. I will probably test all these others in the next few days and compare results in order to learn different ways of thinking. Ifor's is pretty unusual and interesting. Not sure if I understand it, though.

Duane
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2010-04-16 : 03:10:07
IFor:

"In SQL2008, MERGE is safer than an UPDATE FROM with a JOIN."

I'd be interested to know what sort of things are you concerned about in saying MERGE is "safer"
Go to Top of Page

Ifor
Aged Yak Warrior

700 Posts
Posted - 2010-04-16 : 07:24:37
quote:
Originally posted by Kristen

IFor:

"In SQL2008, MERGE is safer than an UPDATE FROM with a JOIN."

I'd be interested to know what sort of things are you concerned about in saying MERGE is "safer"


I am concerned about a one to many relationship between the table being updated and the table being joined to. If this happens with an UPDATE statement, then the last value in the joined table will be used for the update and no error will be thrown. As order in SQL is not guaranteed, this value will be non-deterministic which can cause all sorts of ‘random bugs’ in production systems. This is difficult to pick up as test systems tend to have low usage in which case the last value entered is normally chosen.

In contrast, the MERGE statement will throw an error if a one to many relationship exists.
The standard ANSI UPDATE will also throw an error if a subquery produces more than one result but all the subqueries can get very inefficient.

My heuristic is:
1. if working with SQL2008 then enforce the use of MERGE.
2. if working will previous versions of SQL always make sure that an experienced SQL developer eye-balls all UPDATE FROMs with a JOIN to make sure that there are no one to many relationships.
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2010-04-16 : 07:33:05
" the MERGE statement will throw an error if a one to many relationship exists"

Didn't know that. Definitely handy ... although there are no one:many relationships in any of my UPDATE statements. Guaranteed. </Thud!>
Go to Top of Page

Ifor
Aged Yak Warrior

700 Posts
Posted - 2010-04-16 : 08:11:36
quote:
Originally posted by Kristen

" the MERGE statement will throw an error if a one to many relationship exists"

Didn't know that. Definitely handy ... although there are no one:many relationships in any of my UPDATE statements. Guaranteed. </Thud!>


I am glad that an humble journeyman could assist an esteemed master!
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2010-04-16 : 08:16:20
You are too modest!
Go to Top of Page

Asken
Starting Member

38 Posts
Posted - 2010-04-16 : 08:46:24
Safer but slower... MERGE uses full scans of both tables which should be avoided at all cost.

When reading up on it MS recommends that you create "unique and covering indexes" on the source and "unique clustered index" on the target which in principal means that you should use UPDATE FROM in most cases if you're already sure of the join is on a clustered unique index which waiver the one-many concern.

Reporting & Analysis Specialist
Helping others helps me get better...
Go to Top of Page

Ifor
Aged Yak Warrior

700 Posts
Posted - 2010-04-16 : 09:21:48
quote:
Originally posted by Asken

Safer but slower... MERGE uses full scans of both tables which should be avoided at all cost.

When reading up on it MS recommends that you create "unique and covering indexes" on the source and "unique clustered index" on the target which in principal means that you should use UPDATE FROM in most cases if you're already sure of the join is on a clustered unique index which waiver the one-many concern.

Reporting & Analysis Specialist
Helping others helps me get better...


Asken,

Thanks for the information. When I get time I will look into it.

I seem to remember running tests which indicated that MERGE was slightly more efficient - I will see if I can find them.
Go to Top of Page

duanecwilson
Constraint Violating Yak Guru

273 Posts
Posted - 2010-04-16 : 11:59:03
It looks as though I have opened up a can of worms here. But it is very interesting, as I am still learning. One thing is sure, though, and it is that I know enough to get me into trouble, but not quite enough to get me out of it. Thanks for the insights.

Duane
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2010-04-16 : 13:28:09
Two goods points there
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts
Posted - 2010-04-16 : 14:16:59
quote:
Originally posted by Kristen

" the MERGE statement will throw an error if a one to many relationship exists"

Didn't know that. Definitely handy ... although there are no one:many relationships in any of my UPDATE statements. Guaranteed. </Thud!>
Yeah, that is way a lot of people want MS to deprecate the UPDATE X FROM ... syntax, because it is not ANSI compliant and it can cause a single row to be updated multiple times with, as pointed out, possible unexpected results.

As far as your update statements go, that's a good and rare thing. I've had people I work with get "strange" results with updates and they insisted that there were not multiple updates happening. I suggested they use a merge to prove it (even when a simple select will show that). And they are always surprised when the MERGE statement blows up on them.
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2010-04-16 : 15:06:07
I will repeat my frequent Rant!

Why-oh-why no LINT, or STRICT mode, that would alert to such things during DEV?
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site