CODEWITHSUNDEEP
phpmysqlsql
Krishna Iyer
Krishna Iyer

Reputation: 1

Count duplicates and update table with a single query

I have a table which has several thousand records. I want to update all the records which have a duplicate firstname How can I achieve this with a single query? Sample table structure:

Fname varchar(100)
Lname varchar(100)
Duplicates int

This duplicate column must be updated with the total number of duplicates with a single query. Is this possible without running in a loop?

Upvotes: 0

Views: 947

Answers (4)

Berry Langerak
Berry Langerak

Reputation: 18859

I have a table which has several thousand records. I want to update all the records which have a duplicate firstname How can I achieve this with a single query?

Are you absolutely sure you want to store the number of the so called duplicates? If not, it's a rather simple query:

SELECT fname, COUNT(1) AS number FROM yourtable GROUP BY fname;

I don't see why you would want to store that number though. What if there's another record inserted? What if there are records deleted? The "number of duplicates" will remain the same, and therefore will become incorrect at the first mutation.

Upvotes: 2

Nicola Cossu
Nicola Cossu

Reputation: 56357

update table as t1
inner join ( 
select
fname,
count(fname) as total
from table
group by fname) as t2
on t1.fname = t2.fname
set t1.duplicates = t2.total

Upvotes: 2

dweiss
dweiss

Reputation: 832

Create the column first, then write a query like:

UPDATE table SET table.duplicates = (SELECT COUNT(*) FROM table r GROUP BY Fname/Lname/some_id)

Maybe this other SO will help?

How do I UPDATE from a SELECT in SQL Server?

Upvotes: 0

ethrbunny
ethrbunny

Reputation: 10469

You might not be able to do this. You can't update the same table that you are selecting from in the same query.

Upvotes: -1

Related Questions