All Forums Database
aolympio 25 posts Joined 04/09
26 Jun 2009
SELECT as a field into an INSERT

Hi guys!I can't run this query:INSERT INTO TABLE1 (ID, FIELD1,FIELD2)VALUES(SELECT MAX(ID) + 1 FROM TABLE2, 'Name', '001');What can I do to fix and run this query?Thanks for help,Anderson

Jim Chapman 449 posts Joined 09/04
26 Jun 2009

ANSI/ISO (and Teradata) SQL syntax rules require parentheses around the scalar subquery:INSERT INTO TABLE1 (ID, FIELD1,FIELD2)VALUES( (SELECT MAX(ID) + 1 FROM TABLE2), 'Name', '001');This will work in Teradata 13.0 but probably not in earlier Teradata versions.

aolympio 25 posts Joined 04/09
26 Jun 2009

Thank you so much Jim!!Kind Regards,Anderson

Adeel Chaudhry 773 posts Joined 04/08
28 Jun 2009

Hello,You can also use the following:INSERT Table1 (ID, Field1, Field2)SELECT MAX(ID) + 1 AS "ID" ,'Name' AS "Field1" ,'001' AS "Field2"FROM Table2;HTH!Regards,Adeel

-- If you are stuck at something .... consider it an opportunity to think anew.

aolympio 25 posts Joined 04/09
29 Jun 2009

Very interesting this another form.Thank you so much!!!Anderson

You must sign in to leave a comment.