Consider the situation below:
CREATE TABLE PostTags
(
userid int DEFAULT NULL,
tstamp int DEFAULT NULL,
feed varchar(4)
);
INSERT INTO PostTags VALUES (1,1, "edi");
INSERT INTO PostTags VALUES (1,2, "np");
INSERT INTO PostTags VALUES (1,1, "oth");
INSERT INTO PostTags VALUES (3,4, "pq");
INSERT INTO PostTags VALUES (1,2, "di");
'Timestamp' and 'userid' in my original problem have real datatypes, but I am simplifying the problem here for easy understanding.
It is known that for a given timestamp, a user can only perform one type of feed. If I know that I want to keep feeds of a particular type say "edi" and get rid of feeds of other type like "oth" (in this case, "oth" is the second value for userid 1 and timestamp 1) what type of query can I write?
I tried to give alter ignore to see what it would delete and what it would keep, but it gave me an error:
alter ignore table PostTags add unique index ix_mm (userid, tstamp);
Error: Schema Creation Failed: Duplicate entry '1-1' for key 'ix_mm':
Any suggestions on how I can proceed are greatly appreciated! Thanks!
