Select stmt in an Insert stmt 1

[luvcal]

I have a reference table with values already inserted and I want to insert the appropriate value into another table according to the user input. Can I use a select statement within my insert such as the following?? The SELECT query gives me just what I want, but DB2 won't let me do it:

INSERT INTO tbl1 VALUES('val1','val2','valn',
SELECT val_id FROM tbl2 WHERE &quot;value1 < userinput&quot; AND &quot;value2 > userinput&quot;

Thanks for any and all help!!

 

[gregsimpson]

luvcal,

you appear to be attempting to insert into 3 fields but only selecting from one. Yor not providing values for your 2nd and 3rd fields. I would expect to see something similar to the following.

INSERT INTO T1
SELECT
keyField,
Field1,
Field2
FROM
T2
WHERE
VALUE1 = 'GIVEN' and
VALUE2 = 'GIVEN'

Greg

 

[MarcLodge]

Luvcal,
You cannot have the VALUES clause with a SELECT and must include all the data that you require into the SELECT. Taking your statement, it should read:

INSERT INTO tbl1
SELECT 'val1'
,'val2'
,'valn'
,val_id
FROM tbl2
WHERE value1 < userinput
AND value2 > userinput

Hope this helps
Marc

 

[luvcal]

Thanks for the replies. The problem with the solution is that val1, val2, valn are not in tbl2, they are also user input. The value I want to stick into val_id is a salary range. When the user inputs his salary (e.g. $25000), the select stmt queries the Salary table (tbl2 in my example) and inserts the appropriate range (smallint) into the val_id field of the Customer table (tbl1 in my example). All the other fields in the Customer table (tbl1) are things like social sec number, address, phone, etc., which have nothing to do with the Salary table(tbl2). So, is this still possible?? Thanks again for all the help.

 

[MarcLodge]

Luvcal,
What you've described will work fine. The user input fields val1, val2 etc. are not expected to be in tbl2, they are just input fields to tbl1. The SELECT can be run stand-alone to check that it would fulfil the expectations of the insert if you were doing it as a SELECT into tbl VALUES statement.

Marc

 

[gregsimpson]

luvcal,

I would say you should do your update statement in the normal way, without any reference to table2.

Have a trigger defined which sets the correct value into val_id depending on your current rules.

 

[luvcal]

MarcLodge,
I tried to rewrite the insert stmt as you said and the syntax is coming out wrong. Here are the table defs, insert stmt and the error:

CREATE TABLE customer(SSN integer not null,Fname char(20),Lname char(30),Phone char(10),Sal_range smallint,Start_date date);

CREATE TABLE salary_range(range_id smallint , low_range integer not null,high_range integer not null);

INSERT INTO customer SELECT 123456789,'John','Doe','555-555-5555',range_id FROM salary_range WHERE &quot;low_range < 57600&quot; AND &quot;high_range > 57600&quot; , '05/19/2001';

SQL0104N An unexpected token &quot;AND&quot; was found following &quot;&quot;low_range < 57600&quot;&quot;.
Expected tokens may include: &quot;(&quot;. SQLSTATE=42601

This seems like a simple sytax problem, but I've tried different combos of &quot;&quot; and '' around the comparison clauses to no avail. Thanks for all your help.

 

[MarcLodge]

remove the apostrophes, I think as low_range and high_range are db fields and do not require them
Marc

 

[luvcal]

MarcLodge,

You can tell me to buzz off any time...thanks for being so helpful. I removed the quotes, but now get the error:

SQL0104N An unexpected token &quot;57600&quot; was found following &quot;600 AND high_range >&quot;. Expected tokens may include: &quot;(&quot;. SQLSTATE=42601

I tried single and double quotes around 57600, >, <, etc, but nothing seems to work. Help if you can. Thanks a million.

 

[MarcLodge]

Luvcal,
I shan't be telling you to buzz off, but if we do get it working, I'd love a star!

As far as I can see low_range and high_range are both integers so require no messing about with quotes. I think the problem is the placement of the date field and think the statement should read:

INSERT INTO customer
SELECT 123456789
,'John'
,'Doe'
,'555-555-5555'
,range_id
,'05/19/2001'
FROM salary_range
WHERE low_range < 57600
AND high_range > 57600

Let me know how you go
Marc

 

[luvcal]

YOU RULE!!! That should have been obvious to me what was going on there at the end! Thanks a million for all the help! As you can see, I gave you a star for each post.

 

[MarcLodge]

Thanks!