Jump to content
Henry Olive

FB3 - Update SQL

Recommended Posts

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)                   

 

Share this post


Link to post

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

 

Share this post


Link to post

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

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

Share this post


Link to post

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

Share this post


Link to post
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.

Share this post


Link to post

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)

.....

 

 

Share this post


Link to post

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.

Share this post


Link to post

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
 

Share this post


Link to post

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

Share this post


Link to post

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
 

 

Share this post


Link to post

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

Share this post


Link to post

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×