CODEWITHSUNDEEP
sql
Subash
Subash

Reputation: 33

Adding a Field value in a table to a value in other table

First of all many thanks to all who are trying to solve many issues all over the world. Now I too have a small issue and I hope it can be answered easily.

I have 2 Tables as below.

**Table-1**
Emp_ID
Emp_Name
Emp_Address
Emp_Deliveries

**Table-2**
Order_ID
Order_Date
Emp_ID
Delivery_Date
Delivery_Status

Table 2 Contains all the deliveries information and Table-1 Contains the Employee Master Data. Now I want to add the count of deliveries done by each employee in table 2 to the Emp_Deliveries field in table-1. For Example Emp10001 has a value of 3 in Emp_Deliveries fielld I table-1 and now he has delivered 2 orders which is available in table-2, I want to add those 2 to Emp_Deliveries field in table-1. Can you please help me out with this scenario.

Upvotes: 0

Views: 40

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

You can do this as a select:

select e.emp_id, (e.emp_deliveries + coalesce(d.cnt, 0)) as emp_deliveries
from table1 e left outer join
     (select emp_id, count(*) as cnt
      from table2 d
      group by emp_id
     ) d
     on e.emp_id = d.emp_id;

If you want to actually update the values:

update table1
    set emp_deliveries = (emp_deliveries +
                          (select count(*)
                           from table2
                           where table2.emp_id = table1.emp_id
                          ));

There are other ways to phrase these queries (particularly the second one). But these are standard SQL so should work in any database.

Upvotes: 1

Related Questions