Solved

INSERT INTO and subquery with a join and complex where clause

Posted on 2010-08-18
14
415 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

In this article I will describe the Detach & Attach 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.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

810 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