Question 183780 in Mysql Question posted in
The official MySQL documentation can be found here.

how to update specific record in duplicate records in mysql

I have a table in MySQL database. which is having 3 records shown below.
The table :

columns name address phone comments
1st record raj Chennai 232329 null
2nd record raj Chennai 232329 null
3rd record raj Chennai 232329 null

I want to update comments column as duplicate records for two records only.
The output should be like this….
The table

columns name address phone comments
1st record raj Chennai 232329 duplicate records
2nd record raj Chennai 232329 duplicate records
3rd record raj Chennai 232329 null

pls tell me how to do this in MySQL.

Tags:

2

Answers


  1. 0 
    UPDATE your_table
SET comments = 'duplicate records'
WHERE (name, address, phone) IN (
SELECT name, address, phone
FROM your_table
GROUP BY name, address, phone
HAVING COUNT(*) > 1
);
    Login or Signup to reply.
  2. 0

    This can be done as follows :

    First, we need to most recent record for each group:

    select name, address, phone, max(id) as id
from mytable
group by name, address, phone
having count(*) > 1

    Then we use left join to identify all records except the latest id of that group :

    update mytable t
left join (
  select name, address, phone, max(id) as id
  from mytable
  group by name, address, phone
  having count(*) > 1
) as s on s.name = t.name and s.address = t.address and s.id = t.id
set t.comments = 'duplicate records'
where s.id is null

    Demo here

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search