SQL Subqueries? 1

[rpangel]

Hello everyone,

I'm trying to create a couple of SQL query that I need some help with...

I'm trying to create a SQL query that will do mass inserts into a table. I'm using a select statement with an embedded insert statement, but I realized that the insert statements allow one insert at a time and I have over 300 values that need to be inserted... does anyone know another, easier, or better way?

The other thing is, that I'm trying to also update/compute a field in one table (Table A) using the value from a field in another table (Table B) and subtracting it from say '10000' or something. I've tried to use a select statement to select the necessary fields from both tables then I tried to use an update subquery to do the computational stuff. ex:
select a.element, a.key, b.keys_avail
from table_a a, table_b b
where a.element=b.element
update table_b
set b.keys_avail=10000 - a.key <-- this is where i'm having the problem!
where ......blah,blah,blah..

so anyways if anyone can suggest a easier or better way to do this also i'd appreciate it.

Thanks
angel

 

[tlbroadbent]

The problem is that you have the statements switched around. You can use the SELECT query as a sub-query for UPDATE or INSERT queries but UPDATE and INSERT queries do not return results so they cannot be sub-queries.

You want to do something like the following query.

UPDATE table_b
SET b.keys_avail=10000 - a.key
FROM table_a a INNER JOIN table_b b
ON a.element=b.element

I don't know where you got the idea that &quot;insert statements allow one insert at a time.&quot; This is simply not true. You can insert multiple columns and multiple rows with one insert statement. For example;

INSERT table_b
SELECT a.element, a.key, 10000-a.key
FROM table_a

Hope this helps. Terry

http://members.home.net/tlbroadbent/sqlarticles.htm

&quot;I'm not dumb. I just have a command of thoroughly useless information.&quot; - Calvin, of Calvin and Hobbes

 

[rpangel]

THANK YOU, THAT WAS A LOT OF HELP

 

[rpangel]

What I'm trying to do it calculate the number of keys available (KEYS_AVAIL) after subtracting the NEXT_VALUE, in the CTRL_KEY_VALUE table, from MAX_KEY_VALUE, in the OFFICE_CODES table, to find out how many keys are left and to return the value in the KEYS_AVAIL field in the OFFICE_CODES table for the corresponding ELEMENT_NAME.

This is the code that I've gotten so far:
UPDATE OFFICE_CODES
SET OFFICE_CODES.KEYS_AVAIL = OFFICE_CODES.MAX_KEY_VALUE - CTRL_KEY_VALUE.NEXT_VALUE
FROM OFFICE_CODES INNER JOIN CTRL_KEY_VALUE
ON OFFICE_CODES.ELEMENT_NAME = CTRL_KEY_VALUE.ELEMENT_NAME ;

And this is the error I keep getting:
Syntax error (missing operator) in query expression ' OFFICE_CODES.MAX_KEY_VALUE - CTRL_KEY_VALUE.NEXT_VALUE
FROM OFFICE_CODES INNER JOIN CTRL_KEY_VALUE
ON OFFICE_CODES.ELEMENT_NAME = CTRL_KEY_VALUE.ELEMENT_NAME ;

Does anyone have any suggestions or happen to know why it's doing that?

Thanks in advance,
Angel