What would be the most efficient way in Oracle database to update a column for each row from a join query result?

Question

First let me describe the scenario:

Say I have two tables PRODUCTS and CUSTOMERS, which could be described as shown below:

PRODUCTS table:

+----------+
| PRODUCTS |
+----------+
| ID       |
| CARD_ID  |
+----------+

And CUSTOMERS table:

+-----------+
| CUSTOMERS |
+-----------+
| ID        |
| APP_ID    |
| CARD_ID   |
+-----------+

All these fields are of number type.

These two tables are fairly large and already contain good amount of data. In the CUSTOMERS table, the CARD_ID is the same CARD_ID from PRODUCTS table. There is a one-to-one relation between these tables, that is, only one CARD_ID can be used by one customer and no CARD_ID is used by more than one customer. But some customer may have not used any product yet, i.e. have null as CARD_ID.

I would like to add a back reference for CUSTOMERS table in our PRODUCTS table (for applications sake). So I did something like:

ALTER TABLE PRODUCTS ADD CUSTOMER_ID NUMBER DEFAULT -1 NOT NULL;

Now, as there are already existing data, I also need to write a migration query. That is, if a customer is using a card id C, then I need to add that customer id in C's customer id field for all such existing matches.

I have thought of using something like this, but my understanding is, the inner select query is an inner join here, so I am not sure if that will be efficient in this case.

I also though about using the "with" clause, but I think "with" only works with select queries. [UPDATE] Found this using "with" clause but is this a good way?

Help me to figure out how this should be done efficiently. For example I would not like to update the rows in PRODUCTS which is not being used by any customer at this moment.

Database: Oracle 12c.

Thanks in advance.

[EDIT] Just wanted to mention that, I ended up using PL/SQL for this. In PL/SQL this is quite easy. Also, easier for us to put that through a flyway migration system.

DECLARE

CURSOR C IS SELECT customer.APP_ID, customer.CARD_ID FROM CUSTOMERS customer INNER JOIN PRODUCTS prod on customer.CARD_ID = prod.CARD_ID;

BEGIN

FOR rec IN C
LOOP
    UPDATE PRODUCTS p SET p.CUSTOMER_ID = rec.APP_ID WHERE p.CUSTOMER_ID is null and p.CARD_ID = rec.CARD_ID;

END LOOP;

END;

Just yet another way of accomplishing stuffs. And this is suitable for my particular application. Is this the best way? May be, may be not!

Answer 1

I agree that you should avoid updating the products table. I would even not add the customer_id column to it: it will not only make updates to the customers.card_id column take more time -- as it also requires an update on the products table at the same time -- it may even lead to inconsistency if the implementation is not watertight.

Instead I would suggest to create a database view that joins the customer_id with the products columns:

create view vw_products as
select      products.id, products.card_id, customers.customer_id
from        products
inner join  customers
        on  customers.card_id = products.card_id;

That way, you don't actually store the customer_id, but can select from this view instead of the original products table, getting what you want. Also, there is no migration to be done on the table data.

Even if there is a huge volume of data, this will perform well if you have indexes defined on the card_id columns in both tables.

If for some reason you really need to add the customer_id column to the products table, then update that new column as follows:

UPDATE products
SET    customer_id = (
    SELECT customer_id
    FROM   customers
    WHERE  customers.card_id = products.card_id);

NB: this database design looks counter-intuitive: a one-to-one relationship between customers and products is a guarantee for this business to go bankrupt.

Answer 2

smth is wrong in database design

Each customer can buy more than one product, each product can be bought by more than one customer, many-to-many relation, it would be better create new table customer_products. If you need add product - just insert it, updates will be pretty simple same as deletes

create table product(id int);
alter table PRODUCT
  add constraint product_pk primary key (ID);

create table CUSTOMERS(id int, app_id int, card_id int);
alter table CUSTOMERS
  add constraint CUSTOMERS_pk primary key (ID);

create table customer_products(product_id int, customer_id int, <...other fields if necessary>);

alter table CUSTOMER_PRODUCTS
  add constraint fk_customer_products_prod_id foreign key (PRODUCT_ID)
  references product (ID);

alter table CUSTOMER_PRODUCTS
  add constraint fk_customer_products_cust_id foreign key (customer_ID)
  references customer (ID);