Solved

INSERT INTO and subquery with a join and complex where clause

Posted on 2010-08-18
14
411 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
 
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

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.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
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

744 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now