current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 4 down vote favorite

I am executing the following merge statement in SQL Server 2008:

MERGE 
PopulationData AS a
USING ImagesData AS b
ON a.ID = b.ID
WHEN MATCHED THEN
UPDATE SET a.SURNAME = 'joe123'
WHEN NOT MATCHED THEN INSERT(a.ID,a.SURNAME)
VALUES (12454,'joe123');

I have the following error:

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'AS'.

Can anyone tell me where the syntax error.

share|improve this question
    
What happens if you add a semicolon before the MERGE keyword?? MERGE is one of those statements that insists on being the first statement in a T-SQL batch.... maybe you have other statements before this??? –  marc_s Nov 17 '11 at 16:19
1  
Thanks marc_s, the problem that i was executing the query on sql server management studio 2008 but i was connecting to sql server 2005 database on another server. Now it is fixed. –  Joe123 Nov 18 '11 at 7:21

2 Answers 2

up vote 9 down vote accepted

Parsing your query in SQL Management Studio gives me the following error:

Msg 10739, Level 15, State 1, Line 7 The insert column list used in the MERGE statement cannot contain multi-part identifiers. Use single part identifiers instead.

I then remove the identifiers...

MERGE 
PopulationData AS a
USING ImagesData AS b
ON a.ID = b.ID
WHEN MATCHED THEN
UPDATE SET a.SURNAME = 'joe123'
WHEN NOT MATCHED THEN INSERT(ID,SURNAME)
VALUES (12454,'joe123');

...and the query parses successfully. Therefore, the syntax error is almost certainly not coming from your MERGE statement. Are you really executing only the statement you posted, or is it part of a larger script or procedure? And if you double-click the error message, it should highlight the line where the syntax error is (at least with SQL 2008).

Update: I noticed that you have tagged the question for SQL 2005 and 2008, but MERGE is only supported in SQL 2008. Parsing the query under SQL 2005 gives the syntax error.

share|improve this answer
    
Thanks for reply. It's the query that i am executing and when double-click on the error message it highlight the line "PopulationData AS a" –  Joe123 Nov 17 '11 at 15:59
    
If the PopulationData contains other not null fields, will this result to this error? –  Joe123 Nov 17 '11 at 16:07
    
It looks like you are executing the query in a SQL 2005 database. –  Pondlife Nov 17 '11 at 16:18
    
Thank you very much Pondlife. You are right, the sql server that i was connecting was 2005. my current sql management studio was 2008. I just connected to a 2008 database and it is working. Thanks again –  Joe123 Nov 18 '11 at 7:18
    
@Joe123: You should probably accept this answer as it seems to have resolved all the issues pertaining to your question. –  Andriy M Nov 18 '11 at 9:35
up vote 0 down vote

the problem that i was executing the query on sql server management studio 2008 but i was connecting to sql server 2005 database on another server. Now it is fixed

share|improve this answer
    
You should accept Pondlife's answer then. –  Martin Smith Nov 25 '11 at 15:16

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.