Select subquery in an insert statement

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Select subquery in an insert statement

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-10-18 : 09:17:04

Stuart writes "Hi-

Is there a way to use a currently selected value in a select statement as a variable in the where clause for a subquery in the same statement.

For example, in the small query below, I'd like to use "myNumber" as a variable in the subquery's WHERE statement to return a unique value.

INSERT INTO myNewTable
(SELECT myNumber,
(SELECT myOtherData from myOldTable
WHERE myNumber=myNumber
AND <other criteria>)
FROM myOldTable)

If I leave the query just like this, and I have duplicate myNumbers in the myOldTable table, the second select subquery generates too many results to be used in the parent select statement. I need to use the "myNumber" value that's currently being selected as one of the search criteria for the myOtherData value.

Please help!
Many thanks,
stuart"

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-10-18 : 09:29:19

Try:

INSERT INTO myNewTable
SELECT A.myNumber, (SELECT myOtherData from myOldTable B Where B.myNumber = A.myNumber)
FROM myOldTable A

but I think this would be better

INSERT INTO myNewTable
SELECT A.myNumber, B.myOtherData
FROM myOldTable A INNER JOIN myOldTable B ON A.myNumber = B.myNumber

ashok
Yak Posting Veteran

57 Posts

Posted - 2002-10-18 : 09:33:07

I think you are trying to do a 'correlated subquery'.
something like :

SELECT au_lname, au_fname
FROM authors
WHERE 100 IN
(SELECT royaltyper
FROM titleauthor
WHERE titleauthor.au_ID = authors.au_id)

note that : the authors.au_id in the subquery is from the parent query.

you can search books online for more details.

-ashok
http://www.unganisha.org

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources