0 votes
1 view
in SQL by (20.3k points)

I have a table with the following fields:

id (Unique)

url (Unique)

title

company

site_id

Now, I need to remove rows having same title, company and site_id. One way to do it will be using the following SQL along with a script (PHP):

SELECT title, site_id, location, id, count( * ) 

FROM jobs

GROUP BY site_id, company, title, location

HAVING count( * ) >1

After running this query, I can remove duplicates using a server side script.

But, I want to know if this can be done only using SQL query.

1 Answer

0 votes
by (40.3k points)

You can add the UNIQUE index on the 3 columns.

Note: When you use the ALTER statement then you need to include IGNORE keyword.

Query:

ALTER IGNORE TABLE jobs

ADD UNIQUE INDEX idx_name (Site_Id, title, company);

This will help to drop all the duplicate rows. Additional benefit of using INSERT is duplicates will be removed.

You can refer to this video to understand DROP in detail.

Related questions

0 votes
1 answer
0 votes
1 answer
0 votes
1 answer
asked Aug 1, 2019 in R Programming by ashely (43.2k points)
0 votes
1 answer
Welcome to Intellipaat Community. Get your technical queries answered by top developers !


Categories

...