Home Add unique constraint by removing duplicates
Reply: 0

Add unique constraint by removing duplicates

user779
1#
user779 Published in May 25, 2018, 6:55 pm

My mysql table looks like the following:

CREATE TABLE "my_table" (
  "id" int(11) NOT NULL AUTO_INCREMENT,
  "content" blob NOT NULL,
  "business_id" varchar(255) NOT NULL
  PRIMARY KEY ("id")
) ENGINE=InnoDB AUTO_INCREMENT=1000 DEFAULT CHARSET=latin1;

This table can contains several lines with the same "business_id". I want to make the business_id column unique. For this, I want to delete all lines which share a business_id except the last one.

For example, if I have the following lines

10  'hello' '5'
...
21  'hello' '5'
...
350 'hello' '5'

I want to delete lines of id 10 and 21.

I'm stuck on how to write the delete query. I don't know how to express that constraint.

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.310791 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO