At 10:21 AM 6/08/2008, you wrote:

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

The error comes from the fact that your inner join criterion supp_code is not unique in FARM. This would be obvious if you were using explicit JOIN syntax instead of the deprecated SQL-89 syntax. The 'update' part of UPDATE OR INSERT won't work with this non-unique set without specifying a match for the primary key.

As a further comment, your metadata structure for FARM doesn't make sense. The PK should be on FARM_CODE alone and the UNIQUE constraint should be applied to (SUPP_CODE, FARM_CODE).

However, your syntax is all wrong for inserting from the output of a SELECT statement. It's not a subquery, but a query expression whose output has this explicit usage in INSERT syntax, *replacing* the VALUES() clause. Trial and error will tell whether it is allowable for UPDATE OR INSERT.

The puzzle is why you are getting that exception, rather than "Token unknown...". Maybe this should be raised in the Tracker as it looks like wrong prioritisation in the exception stack.

Try it again with the following:

update or insert into farm_products (farm_code,prod_code)
select f.farm_code, 91
from farm f
join supplier s
on f.supp_code=s.supp_code
where s.fuel_region='SB'
matching (supp_code, farm_code)

Where I suspect this could come unstuck is that the supp_code is missing from both the columns list and the output set.

1. Make sure that you don't use a RETURNING clause in this statement (it will barf unequivocally on the multiple rows returned from the query expression).

2. If you find that it won't work, try bracketing the query expression as follows:

update or insert into farm_products (farm_code,prod_code)
(select f.farm_code, 91
from farm f
join supplier s
on f.supp_code=s.supp_code
where s.fuel_region='SB')
matching (supp_code, farm_code)

3. If it still won't work (the parser barfs on the bracketed query expression), then take careful note of the exception returned and post an improvement request in the Tracker, along with the metadata, a good description of what you needed to do, and the details of your DSQL..

You wrote:

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

You surely can but possibly not using UPDATE OR INSERT. You can write an executable SP to do precisely what you want.

> This particular database has been running at a customer site for some 13 months.......This table has been used from day one.<

Nevertheless, I don't think these tables are correctly structured to capture the relationships between farms, suppliers and products. The supp_code should not be part of the PK of FARM; but even as a FK, it constrains the structures such that either a) a farm can have only one supplier; or b) you have to have multiple FARM records for each farm.

I think SUPP_CODE does not belong in the FARM record at all, but in a separate intersection table between FARM and SUPPLIER, that has FKs to both tables.

./heLen