Helen Borrie escreveu:

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

Seems to be the reason for the error.

The 'update' part of UPDATE OR INSERT won't work with this non-unique
set without specifying a match for the primary key.

>

No subquery (as a value expression) can return more than one row.

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

Reasons for not allow UPDATE OR INSERT ... SELECT was on this list some
days ago.

You should use MERGE, if you want to UPDATE OR INSERT multiple rows.


Adriano