Hi - I'm using FB 2.1.0 on W2003 server with Ibexpert as the
development tool. This particular database has been running at a
customer site for some 13 months. They now want to add several hundred
records to this one table (which they can do individually through a
maintenance routine) but have asked whether I can add them quicker in
bulk, which I can, I'm sure. This table has been used from day one.

CREATE TABLE FARM_PRODUCTS (
FARM_CODE INT_CODE /* INT_CODE = INTEGER default 0 NOT NULL */,
PROD_CODE INT_CODE /* INT_CODE = INTEGER default 0 NOT NULL */,
INITIAL_MOISTURE DECIMALS_2 /* DECIMALS_2 = NUMERIC(9,2) default 0.00 */,
SAMPLE_INTERVAL DECIMALS_0 /* DECIMALS_0 = INTEGER default 0 */,
NEXT_SAMPLE DECIMALS_0 /* DECIMALS_0 = INTEGER default 0 */
);
/* Primary Keys
*/
/******************************************************************************/

ALTER TABLE FARM_PRODUCTS ADD CONSTRAINT PK_FARM_PRODUCTS PRIMARY KEY
(FARM_CODE, PROD_CODE);

I have a second table - SUPPLIER
ALTER TABLE SUPPLIER ADD CONSTRAINT PK_SUPPLIER PRIMARY KEY (SUPP_CODE);
(subset below)
Supp_code .... fuel_region
1 SB

and a third table - FARM
ALTER TABLE FARM ADD CONSTRAINT PK_FARM PRIMARY KEY (SUPP_CODE, FARM_CODE);
ALTER TABLE FARM ADD CONSTRAINT UNQ1_FARM UNIQUE (FARM_CODE)
(subset below)
farm_code supp_code
101 1
102 1


FARM_PRODUCTS
Some existing records:-
farm_code prod_code initial_moisture sample_interval next_sample
101 12 25.00 1 1
101 13 23.00 10 1

I want to add new records for every farm where the supplier.fuel_region='SB'.
The prod_code will be 91 e.g.
farm_code prod_code initial_moisture sample_interval next_sample
101 91 25.00 1 1

I tried this (and various other versions)
update or insert into farm_products(farm_code,prod_code)
values(
(select farm.farm_code
from farm, supplier
where farm.supp_code=supplier.supp_code
and supplier.fuel_region='SB'),91)
but get the error "multiple rows in singleton select."

I have tried with and without the "matching" command with no difference.
I'm confused by the fact that the supplier.supp_code is a PK and
therefore unique and also the farm.farm_code.

I can get around this by creating a temporary table and populating it
with the data I need to, then inserting that data into the real table.
But the "update or insert into" statement is designed to do just that
and would be really useful in future, so if anyone could give me a
guide I'd be grateful.
Regards
Alan
--
Alan J Davies
Aldis