FB3 - Update SQL

[Henry Olive 5]

I wish everyone a healthy day
In below sql,  i get multiple rows in singleton select error msg 
what is wrong ?

Thank You

UPDATE ITEMS IT 
  SET 
  IT.BOMCOST = (SELECT V.FPRICE FROM VENDOR V 
                           WHERE V.ITEMNO = IT.ITEMNO and V.PREFERRED ='Yes')
  WHERE EXISTS (SELECT 1 FROM VENDOR V WHERE V.ITEMNO=IT.ITEMNO)                   

 

[Serge_G 87]

Hi, without structure of tables ITEMS and VENDOR I should say that there is something missing in the where clause of the update  

 

a guess

 

UPDATE ITEMS IT 
  SET 
  IT.BOMCOST = (SELECT FPRICE FROM VENDOR
                           WHERE ITEMNO = IT.ITEMNO and PREFERRED ='Yes')
  WHERE EXISTS (SELECT 1 FROM VENDOR  WHERE ITEMNO=IT.ITEMNO and PREFERRED='Yes')   

 

[Henry Olive 5]

Thank you so much Serge

It works but it writes same price for every item !!

[Serge_G 87]

Yes, the goal was to show you there is something missing in the where clause

Something like IT.ITEMNO= ????
This why I asked you

20 minutes ago, Serge_G said:

structure of tables ITEMS and VENDOR

Structure and Relations by evidence, And if you give a script to fill with some test datas, better

Edited February 27, 2022 by Serge_G

[Serge_G 87]

Another guess (not tested)  but still lack something

UPDATE ITEMS IT 
  SET 
  IT.BOMCOST = (SELECT FPRICE FROM VENDOR
                           WHERE ITEMNO = IT.ITEMNO and PREFERRED ='Yes')
  WHERE I.ITEMNO=(SELECT ITEMNO FROM VENDOR  WHERE ITEMNO=IT.ITEMNO and PREFERRED='Yes') 

[Guest]

I thought i could spot an error but @Serge_G is correct, we cannot draw conclusion without the structure. For example a PK is always unique and other fields/combinations can have unique constraints. We would at least have to know the combinations of unique fields in your tables. Hence the structure would be helpful.

[Henry Olive 5]

Thank you Serge
VENDOR :

ITEMNO VARCHAR(20) NOT NULL,

CUSTNO INTEGER NOT NULL,

PREFERRED VARCHAR(3)

PK=ITEMNO, CUSTNO

.....

 

ITEMS :

ITEMNO VARCHAR(20) PK,

BOMCOST NUMERIC(15,5)

.....

 

 

[Lajos Juhász 293]

The PK for Vendor is ItemNo, Custno you cannot make it a unique value only by joining it with fields itemno and preferred. You could use min, max or avg functions.

[Henry Olive 5]

Thank you Lajos,

Even though there more than 1 record for the same ItemNo  

(More than 1 Supplier (which is CustNo) for the same ItemNo) 

there must be only 1 supplier (CustNo) with *Preferred = Yes*

Vendor Table for ItemNo AAA

ItemNo ...CustNo.... Preferred

AAA.................1...............Yes

AAA.................2

AAA.................3
 

[Lajos Juhász 293]

In that case it's not true anymore check it with:

 

select itemno, count(CustNo)

from Vendor

where Preferred='Yes'

group by itemno

having count(CustNo)>1

[Serge_G 87]

What a horrid structure !
Before going further, some tips :
Don't use VARCHAR as primary keys , take custom to have an id bigint value filled by a SEQUENCE or generated by default as identity

For the varchar keys create index  i.e  CREATE UNIQUE INDEX IDX_VENDORITEMS ON VENDOR  (ITEMNO, CUSTNO);

Don't use name ITEMNO if it's a VARCHAR, prefer ITEMCODE (prefix NO for numbers)
Don't forget to use Foreign keys when necessary

I'll soon write a better script
 

 

[Serge_G 87]

Here is what I call a script, at bottom the solution you need even if I really dislike all those sub-querys
 

CREATE TABLE ITEMS
(
  ID BIGINT GENERATED BY DEFAULT AS IDENTITY  NOT NULL,
  ITEMNO VARCHAR(20) NOT NULL,
  BOMCOST NUMERIC(15,5),
  CONSTRAINT PK_ITEMS PRIMARY KEY (ID)
);

CREATE UNIQUE INDEX IDX_ITEMSCODE ON ITEMS (ITEMNO);

CREATE TABLE VENDOR   -- bad name here should be VENDOR_ITEMS  somewhere necessits a TABLE VENDOR (id BIGINT GENERATED BY DEFAULT AS IDENTITY  NOT NULL,NAME VARCHAR(80)) 
(
  ID INTEGER GENERATED BY DEFAULT AS IDENTITY  NOT NULL,
  ITEMID BIGINT,
  ITEMNO VARCHAR(20) NOT NULL,
  CUSTNO INTEGER NOT NULL,
  PREFERRED VARCHAR(3),
  PRICE NUMERIC(8,2),
  CONSTRAINT PK_VENDOR PRIMARY KEY (ID)
);

ALTER TABLE VENDOR ADD CONSTRAINT FK_VENDOR_ITEM
  FOREIGN KEY (ITEMID) REFERENCES ITEMS (ID) ON UPDATE CASCADE ON DELETE CASCADE;
CREATE UNIQUE INDEX IDX_VENDORITEMS ON VENDOR (ITEMNO,CUSTNO);

COMMIT; 
-- some test datas
INSERT INTO VENDOR (ID, ITEMID, ITEMNO, CUSTNO, PREFERRED, PRICE) VALUES ('2', '1', '1', '1', 'Yes', '100.00');
INSERT INTO VENDOR (ID, ITEMID, ITEMNO, CUSTNO, PREFERRED, PRICE) VALUES ('3', '1', '1', '2', 'No', '150.00');
INSERT INTO VENDOR (ID, ITEMID, ITEMNO, CUSTNO, PREFERRED, PRICE) VALUES ('4', '2', '2', '2', 'Yes', '150.00');
INSERT INTO VENDOR (ID, ITEMID, ITEMNO, CUSTNO, PREFERRED, PRICE) VALUES ('6', '3', '3', '4', NULL, '10.00');
INSERT INTO VENDOR (ID, ITEMID, ITEMNO, CUSTNO, PREFERRED, PRICE) VALUES ('7', '3', '3', '5', 'No', '12.00');

INSERT INTO ITEMS (ID, ITEMNO, BOMCOST) VALUES ('1', '1', '0.00000');
INSERT INTO ITEMS (ID, ITEMNO, BOMCOST) VALUES ('2', '2', '0.00000');
INSERT INTO ITEMS (ID, ITEMNO, BOMCOST) VALUES ('3', '3', '0.00000');

COMMIT;
-- Now updating
UPDATE ITEMS a
SET a.BOMCOST = (SELECT PRICE FROM VENDOR WHERE ITEMNO = a.ITEMNO and PREFERRED='Yes') 
WHERE EXISTS (SELECT 1 FROM VENDOR WHERE ITEMNO = a.ITEMNO and PREFERRED='Yes')   
  AND a.ITEMNO=(SELECT ITEMNO FROM VENDOR WHERE ITEMNO = a.ITEMNO and PREFERRED='Yes')
COMMIT;

result for  SELECT r.ID, r.ITEMNO, r.BOMCOST FROM ITEMS r

Quote

ID    ITEMNO    BOMCOST
1    1                100.00000
2    2                150.00000
3    3                    0.00000

 

I keep using your ITEMNO and CUSTNO strings, but it's really a bad habit. Try to apply tips upper to have a better database

[Henry Olive 5]

Thankyou SO SO MUCH for your advices & codes