Insert with select on same table.

[jscott]

Hi, I have been working on this isnert for a while, with several differant varieties of the same code with no result except error.
I have tried-insert into abstractor(abID) select (select (concat(substring(abstractor.abFirst, 1, 1), substring(abstractor.abLast, 1, 1), abstractor.abstractorID));

as well as-insert into abstractor(abID) select (select (substring(abstractor.abFirst, 1, 1), substring(abstractor.abLast, 1, 1), abstractor.abstractorID);

and-insert into abstractor(abID) select (select concat (substring(abstractor.abFirst, 1, 1), substring(abstractor.abLast, 1, 1), abstractor.abstractorID));

all return the same message, syntax error.
Can this be done?
Jean

 

[Cagliostro]

as I know after insert should't follow brackets(). If you want so, remove brackets after select or use values instead of select. Ion Filipski

filipski@excite.com

 

[jscott]

I tried the following two with the same syntax error
insert into abstractor(abID) values (select concat (substring(abstractor.abFirst, 1, 1), substring(abstractor.abLast, 1, 1), abstractor.abstractorID));

insert into abstractor(abID) values (concat (substring(abstractor.abFirst, 1, 1), substring(abstractor.abLast, 1, 1), abstractor.abstractorID));

I am not sure the select values will work if I am trying to insert them from the same table. Does anyone know if I can do an insert select from the same table?
If so I need a clue!!!
Thanks
Jean

 

[Cagliostro]

insert into xxx(a,b,c)
select x,y,z from xxx; Ion Filipski

filipski@excite.com

 

[cDevX99]

One suggestion, does not an insert clause need a values section.... insert into test ( cola, colb ) VALUES ( ( select .....) ) ;

Just a thought B-)

 

[Cagliostro]

Ok, I understood what the problem is. This is a recursion in the insert. use a temp table. Ion Filipski

filipski@excite.com

 

[jscott]

Thanks, I think that is the only way.
I didn't want to have to do this every time I add new rows. I was hoping to make it automatic so to speak. When a new person is added through the site I am working on I want this field to generate itself....I want to make it an unique key field. I guess I will have to generate this field's value through my cgi before doing the inserts.
Thanks
Jean