Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 431
  • Last Modified:

INSERT INTO and subquery with a join and complex where clause

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
kkhan7
Asked:
kkhan7
3 Solutions
 
TimBusiness Systems AnalystCommented:
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
 
cyberkiwiCommented:
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
 
incidental74Commented:


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
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

 
Anthony PerkinsCommented:
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
 
cyberkiwiCommented:
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
 
TyryssSkyesCommented:
Cyberkiwi,
Thank you that worked perfectly and easily!
0
 
Anthony PerkinsCommented:
>>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
 
cyberkiwiCommented:
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
 
Anthony PerkinsCommented:
I guess then we will have to agree to disagree.
0
 
Anthony PerkinsCommented:
Now whaever happened to that author ...
0
 
cyberkiwiCommented:
>> 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
 
Anthony PerkinsCommented:
Yes, that is right.
0
 
cyberkiwiCommented:
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
 
Anthony PerkinsCommented:
Fair enough.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now