oracle: update a column with not null value

Question

I wanna update a column of a table:

UPDATE product prod 
SET    prod.prod_supplier_id = (SELECT s.prod_supplier_id 
                                FROM   supplier s 
                                WHERE  s.prodno = prod.prodno
                             )

the SELECT s.prod_supplier_id FROM supplier s WHERE s.prodno = prod.prodno

mustn't return a null result, if it's null, the update will not be made How to do that?

score 4 · Accepted Answer · 2013-12-17 12:26:58Z

4

You need to filter the rows to be updated in the WHERE clause as well:

UPDATE product prod 
SET    prod.prod_supplier_id = (SELECT s.prod_supplier_id 
                                FROM   supplier s 
                                WHERE  s.prodno = prod.prodno
                             ) 
WHERE EXISTS (SELECT 42 
              FROM   supplier s2 
              WHERE  s2.prodno = prod.prodno);

It might be faster using a MERGE (assuming prodno is the primary key in product):

merge into product 
using
(
   select p.prodno,
          s.prod_supplier_id
   from product p
     join supplier s on s.prodno = p.prodno  
) t on (t.prodno = prod.prodno)
when matched then update
   set prod_supplier_id = t.prod_supplier_id;

Not tested!

edited Dec 17, 2013 at 12:26

answered Dec 17, 2013 at 11:23

user330315

Sign up to request clarification or add additional context in comments.

Comments

Hamidreza · Accepted Answer · 2013-12-17 11:27:46Z

1

first of all create a back up table:

CREATE TABLE productBAK AS SELECT * FROM product;

now you can use update query like this:

UPDATE product prod 
SET    prod.prod_supplier_id = (SELECT s.prod_supplier_id 
                            FROM   supplier s 
                            WHERE  s.prodno = prod.prodno and
                            s.prod_supplier_id is not null
                         ) 
WHERE prod.prodno in (SELECT s1.prodno FROM supplier s1 where s1.prod_supplier_id is not null);

answered Dec 17, 2013 at 11:27

Hamidreza

3,1581 gold badge20 silver badges15 bronze badges

Comments

saurabhk · Accepted Answer · 2013-12-17 09:45:17Z

0

The WHERE clause specifies which record or records that should be updated. If you omit the WHERE clause, all records will be updated!

answered Dec 17, 2013 at 9:45

saurabhk

1401 gold badge4 silver badges15 bronze badges

Comments

Robbie Nerve · Accepted Answer · 2013-12-17 10:00:35Z

0

I see prod_supplier_id being a column in both product and supplier.

Can you explain in which table the prod_supplier_id must not be null? Looking at your query you want something like

update product
set prod_supplier_id = (select prod_supplier_id 
                        from supplier 
                        where prod_supplier_id is not null);

Issues you're facing:

all product records will be updated with the same prod_supplier_id
if the subselect statement returns more than one record -let's say you have 5 suppliers, so 5 prod_supplier_id's, the update statement will fail.

Can you tell us:

do you want all your products to be set to 1 supplier?
if not, do you have a list of which product should have which supplier?

R

answered Dec 17, 2013 at 10:00

Robbie Nerve

32 bronze badges

5 Comments

Robbie Nerve Over a year ago

Could you paste the output of the statements DESC PRODUCT and DESC SUPPLIER so we can see table structure?

senior Over a year ago

the prod_supplier_id is the primary key of the table supplier

Robbie Nerve Over a year ago

Hi Neila, could you post the output of DESC PRODUCT and DESC SUPPLIER please? Also, do you store the product id's in the supplier table?

senior Over a year ago

I store the product number in the table supplier, each supplier has one product. when I get many products, if a product has a supplier I wanna set its id(the supplier_id) in the table product, but I don't wanna make extra updates cause I have a trigger which will insert in another table when the table product is updated, just I wanna get the supplier_id after verifying the where condition, if there is a result I ll update the table if its null, I ll not make an extra update to set a null resukt

Robbie Nerve Over a year ago

ok, it's a bit unconventional how your datamodel is set up. I would expect that you stored product information in your product table, including a supplier_id and fill the supplier table with supplier information, without a product_id column in the supplier table. when you set your tables up like that, you will have a 1-to-many relationship between supplier and product. 1 supplier can reference many products, while the other way around, one product will allways be linked to one supplier Maybe you should read a bit about normalisation :-)

Collectives™ on Stack Overflow

oracle: update a column with not null value

4 Answers 4

Comments

Comments

Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related