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.