Using group by to update multiple records in an update query

Question

I have two tables. Table1 has company data (Company ID, Company Name ...), so single record for each company.

Table2 has information about departments in that company (Department ID, Department Name, Company ID, Company Name ... ). So, second table might have n number of records where same company id is used.

Problem is one of our trigger failed to work properly, and no one noticed till now. So, when Company Name was updated in Table1, it never reflected in Table2.

To correct this, I have to do something like the below query:

Update Table2 
Set 
    [Company Name] = (select [Company Name] 
                      from Table1 
                      where Table2.Company ID = Table1.Company ID)
Group By Table2.Company ID

Basically, I am trying to update all records in Table2 to use the same name as Table1, for each record in Table1.

I am a bit confused about how to create the inner select clause.

P.S. Sorry, it might be a bit confusing. Kindly do let me know how to reword it the best.

I don't see a need for the group by and you can simply do a normal inner join without the subquery — Allan S. Hansen, May 04 '16 at 05:31
Remove the group by clause, that query will do what u expected. (But duplicating data - CompanyName - is not a good practice) — Abdul Rasheed, May 04 '16 at 05:33
@KumarHarsh Believe me, it is useful in this specific application. Saves a lot of time in unnecessary joins as company name is used like hundred times. Although, it should be turned into view, if I had my way. Sadly, can't rewrite half the application to use proper database design. :/ — jitendragarg, May 04 '16 at 05:36
@jitendra garg: It is very strange that you have to use a crappy database design, because the joins would be so expensive as you say. That leads me to believe that you are dealing with hundreds and thousands of companies in your table? Billions of department records? With the company_id being primary key in the company table and foreign key in the department table, the join will be extremely fast. Hard to imagine that your data is so huge that this is not the case. — Thorsten Kettner, May 04 '16 at 05:57
Possible duplicate of [SQL update from one Table to another based on a ID match](http://stackoverflow.com/questions/224732/sql-update-from-one-table-to-another-based-on-a-id-match) — Tab Alleman, May 04 '16 at 19:18

score 1 · Accepted Answer · answered May 04 '16 at 05:31

1

Don't need to use group by ...

UPDATE     T2
SET        [Company Nane]   = T1.[Company Nane]
FROM       Table1 T1
INNER JOIN Table2 T2 
        ON T1.[Company ID] = T2.[Company ID]

answered May 04 '16 at 05:31

Squirrel

23,507
4
34
32

Wait, so, this will update n number of records in Table2. Like if I have 5 records in Table2 for Company ID 1, and 4 records for Company ID 2, it will update all 9 records properly? – jitendragarg May 04 '16 at 05:34
Yes. If you only want to updates those without `company name` , add a `where` clause and checks for that – Squirrel May 04 '16 at 05:35
Oh no, I want to update all of them. Like 10 records from master table, to get the data from, and update all 100 records in the secondary table based on the ID. BTW, I think I will have to go through inner joins again, because your query has me confused. It works, but why, no idea. – jitendragarg May 04 '16 at 05:38
Change the query , replace `UPDATE T2 SET ... ` to `SELECT * FROM Table1` and you will see it – Squirrel May 04 '16 at 06:50

score 0 · Answer 2 · answered May 04 '16 at 06:07

As other have mentioned: Remove GROUP BY and your query works fine.

GROUP BY is used to produce a result row in a query that is an aggregate of other rows. E.g. one record per company from your department table with the most busy department per company. You cannot update such a result record, for that record does not exist in the table. You can only update table records.

So remove GROUP BY from your query and you have it straight-forward.

Update DepartmentTable
Set [Company Name] = 
(
  select [Company Name] 
  from CompanyTable
  where CompanyTable.[Company ID] = DepartmentTable.[Company ID]
);

Thanks. Although, I used the inner join option for now. It was only a 2 minute task using query, anyway. :) — jitendragarg, May 04 '16 at 06:22

Using group by to update multiple records in an update query

2 Answers2