SELECT as a field into an INSERT

Database
Enthusiast

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
4 REPLIES
Enthusiast

Re: SELECT as a field into an INSERT

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.
Enthusiast

Re: SELECT as a field into an INSERT

Thank you so much Jim!!

Kind Regards,
Anderson
Teradata Employee

Re: SELECT as a field into an INSERT

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
Enthusiast

Re: SELECT as a field into an INSERT

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

Anderson