Insert and subselect

问题:

INSERT INTO S654321.PERSON
(PNR, FIRSTNAME, LASTNAME)
VALUES
SELECT 32, FIRSTNAME, LASTNAME
FROM S654321.CUSTOMER
WHERE CUSTNR = 'C002'

Returns sqlcode -104 and sqlstate 42601. Do you see the error? The select statement itself is correct.

回答1:

The error is that when you insert records you either use a select, or you specify the values. You don't do both. This is ok

insert into table
(field1)
values
(value1)

as is this:

insert into table
(field1)
select distinct value1
from somewhere

So pick a method.

回答2:

you are mixing two statements, this is what you should do

INSERT INTO S654321.PERSON
(PNR, FIRSTNAME, LASTNAME)
SELECT 32, FIRSTNAME, LASTNAME
FROM S654321.CUSTOMER
WHERE CUSTNR = 'C002'