Solved

INSERT INTO and subquery with a join and complex where clause

Posted on 2010-08-18
14
417 Views
Last Modified: 2013-11-30
Why doesn't this statement work?  I receive  - invalid syntax on line 9 at ').


INSERT INTO document_group (documentid, groupid)
SELECT document.documentid, 199991
FROM document
WHERE 
(SELECT document_group.documentid, document.documentnumber, document.documentid
FROM document_group RIGHT JOIN document ON 
document_group.documentid = document.documentid
WHERE document_group.documentid is null 
and document.documentnumber = 'ABC1212008')

Open in new window

0
Comment
Question by:kkhan7
14 Comments
 
LVL 7

Expert Comment

by:bouscal
ID: 33470113
Not knowing exactly what you're attempting I see a few things.
1. VALUE is not explicitly stated
2. The initial WHERE clause is incomplete, there's no comparison occuring
3. The JOIN will match any NULL in document.documentid to every NULL in document_group.documentid

0
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 167 total points
ID: 33470168
INSERT INTO document_group (documentid, groupid)
SELECT document_group.documentid, 199991
FROM document_group RIGHT JOIN document ON
document_group.documentid = document.documentid
WHERE document_group.documentid is null and document.documentnumber = 'ABC1212008'
0
 
LVL 1

Assisted Solution

by:incidental74
incidental74 earned 166 total points
ID: 33470182


Hello kkhan7,

It looks like you are trying to build a correlated sub-query only you've forgot the keyword EXISTS. Try this:


INSERT INTO document_group (documentid, groupid)
SELECT document.documentid, 199991
FROM document
WHERE 
exists (SELECT document_group.documentid, document.documentnumber, document.documentid
FROM document_group RIGHT JOIN document ON 
document_group.documentid = document.documentid
WHERE document_group.documentid is null 
and document.documentnumber = 'ABC1212008')

Open in new window

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 33471137
Or simply:
INSERT	document_group(
	documentid, groupid)
SELECT	d.documentid, 199991
FROM	DOCUMENT d
WHERE	d.documentnumber = 'ABC1212008'
	AND NOT EXISTS (
		SELECT	1
		FROM	document_group g
		WHERE	g.documentid = d.documentid)

Open in new window

0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33471221
For what it's worth, I made a mistake up top, it should have been

INSERT INTO document_group (documentid, groupid)
SELECT document.documentid, 199991    --- this was wrong
FROM document_group RIGHT JOIN document ON
document_group.documentid = document.documentid
WHERE document_group.documentid is null and document.documentnumber = 'ABC1212008'

As far as performance, NOT EXISTS and LEFT JOIN/IS NULL are equivalent.
0
 

Expert Comment

by:TyryssSkyes
ID: 33471905
Cyberkiwi,
Thank you that worked perfectly and easily!
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 167 total points
ID: 33477833
>>As far as performance, NOT EXISTS and LEFT JOIN/IS NULL are equivalent.<<
I agree and on small tables you will not see much performance difference.  It is just that on large tables the use of EXISTS is dramatically faster.

>>Thank you that worked perfectly and easily!<<
Excellent!  Now please close the question.
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33480228
Anthony,

I created test tables seeded randomly and for up to 100k records in each and about 1k insert required, there was no difference.  Putting both versions together and running in Ctrl-M mode shows the actual execution plan at 50% - 50%.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 33481436
I guess then we will have to agree to disagree.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 33481441
Now whaever happened to that author ...
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33481473
>> I guess then we will have to agree to disagree.

I always think to myself when someone says that - if we do not have an agreement to disagree, does that mean we are in agreement de facto?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 33481601
Yes, that is right.
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33481658
But whether I agree or not to disagree, we still do not agree on the subject.  So why is the agreement necessary?

I would have thought a succinct statement like "we disagree, then" would reflect the situation more accurately.

Don't mind me, just philosophy.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 33481711
Fair enough.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question