Insert subquery syntax [message #4080] |
Thu, 07 November 2002 06:50 |
Graeme
Messages: 4 Registered: April 1999
|
Junior Member |
|
|
Can someone explain the following to me:
INSERT INTO ( SELECT cnt_code FROM
country WHERE continent = 'ASIA' )
VALUES (971, 'SAUDI ARABIA', 'ASIA');
To me this evalutes to
INSERT INTO ( two rows 91 and 65 )
.....
How does this work?
My brain expected the clause to at least return a table name.
Thanks,
Graeme
|
|
|
Re: Insert subquery syntax [message #4086 is a reply to message #4080] |
Thu, 07 November 2002 13:33 |
daudi
Messages: 1 Registered: November 2002
|
Junior Member |
|
|
In unix I have done things similar to the
below ultimately executing the spooled file.
I don't think you can create the insert
you want directly.
connect udxl035/xxxxxx
set head off
set feedback off
set pagesize 0
spool this.sql
select 'insert into '||table_name|| ' where table_name like '||'''ASIA''' from all_tables
/
spool off
quit
|
|
|
Re: Insert subquery syntax [message #4093 is a reply to message #4080] |
Fri, 08 November 2002 01:42 |
Graeme
Messages: 4 Registered: April 1999
|
Junior Member |
|
|
Daudi,
Sorry, you've miss understood. This syntax does work.
It's copied straight out of an OCA study guide. I'm not sure if the syntax is new to 9i or it's been around for a while, but I've never used it until now.
I'm not sure how it does work, though??
Graeme
|
|
|
Re: Insert subquery syntax [message #4099 is a reply to message #4093] |
Fri, 08 November 2002 07:49 |
DLund
Messages: 6 Registered: October 2002
|
Junior Member |
|
|
I am like you then as I have not
used that type of substitution directly.
I like it.
I must still misunderstand though. Why would
you expect to get anything other than the cnt_codes
you ask for? Is that not what you are getting?
I assumed the cnt_codes matched some table names.
|
|
|