Thanks for the pointers on why its not working. I'm looking at this
again and am going to do it with an SP, but wanted to check the
following.

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

Each supplier owns many farms. One farm can only be owned by one supplier

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

This is a one-to-many relationship and I have a direct relationship
using the supplier code as the key in the farm table.

supplier
supp_code
1
farm
supp_code farm_code
1 101
1 102
1 103
supplier
supp_code
2
farm
supp_code farm_code
2 201
2 202
2 203

It doesn't make sense to have a third table referencing these two,
does it? Each row would be a duplicate of these two fields I'm storing
in the farm table (there are other fields too)
--
Alan J Davies


Quoting Helen Borrie <helebor@...>:

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