update table using another table

Question

i have to update total_orders of customers column to be equal to the total number of all the orders placed by the customer(in cust_order) enter image description here

here is what i have tried

update (select * 
        from atish_customer a 
        inner join 
        (
            select cust_nbr,count(cust_nbr) as count_orders
            from atish_cust_order 
            group by cust_nbr
        )c
        on c.cust_nbr=a.cust_nbr) 
set tot_orders=count_orders;

But this is the error i get

ORA-01779: cannot modify a column which maps to a non key-preserved table

donno bro, my friend gave me a word document which had all questions needed to practice sql — user1356163
– user1356163, Commented Jul 25, 2012 at 14:49

Gerrat · Accepted Answer · 2012-07-25 15:18:26Z

2

How about this:

UPDATE customer SET total_orders = (
    SELECT COUNT(*) FROM cust_order 
    WHERE cust_order.cust_nbr = customer.cust_nbr
)

[I'm not sure where your atish_customer and atish_customer_order comes into play...they're not shown in your diagram]

Explanation: Basically the inner select just counts the number of orders from the cust_order table for each cust_nbr. By joining the outer customer.cust_nbr to the inner cust_order.cust_nbr, each [outer] row will be updated with the correct total. This is called a correlated subquery (see here for a short tutorial).

edited Jul 25, 2012 at 15:18

answered Jul 25, 2012 at 14:47

Gerrat

29.9k9 gold badges78 silver badges104 bronze badges

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

5 Comments

user1356163 Over a year ago

yup cud u explain how it works. will accept ur in answer in 3 minutes

user1356163 Over a year ago

oh temp table. i created it before updating

user1356163 Over a year ago

SELECT COUNT(*) FROM atish_cust_order,atish_customer WHERE atish_cust_order.cust_nbr = atish_customer.cust_nbr gives total count. How come?

Gerrat Over a year ago

well, assuming every cust_nbr in atish_cust_order exists in atish_customer, then your SQL basically does an inner join on the two tables, then counts all the rows. You might think of it as enumerating every row in the longer of the two tables - atish_cust_order, then for each of these rows looks up the corresponding cust_nbr in atish_customer. The effect is just to count the total number of records in atish_cust_order.

Gerrat Over a year ago

@ganducoder: I added a link to my answer that hopefully explains the correlated subquery better with an example.

Collectives™ on Stack Overflow

update table using another table

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related