Solved

INSERT INTO and subquery with a join and complex where clause

Posted on 2010-08-18
14
418 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Industry Leaders: 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

Major Incident Management Communications

Major incidents and IT service outages cost companies millions. Often the solution to minimizing damage is automated communication. Find out more in our Major Incident Management Communications infographic.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

737 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