I want to automatically delete rows when the table (shown below) gets a new insert, if certain conditions are met.
When:
Simply, when the rows would become duplicates (except the 'group_id' column) the non null 'group_id' should be deleted, otherwise a row should be updated or inserted.
Is there a way to set this up in mysql (in spirit of "ON DUPLICATE do stuff" combined with unique keys etc.), or do I have to explicitly check for it in php (with multiple queries)?
Additional info: There should always be a row with NULL 'group_id' for every possible 'field' (there's a limited set, defined elsewhere). On the other hand there might not be one with a non null 'group_id'.
CREATE TABLE `Views` (
`user_id` SMALLINT(5) UNSIGNED NOT NULL,
`db` ENUM('db_a','db_b') NOT NULL COLLATE 'utf8_swedish_ci',
`field` VARCHAR(40) NOT NULL COLLATE 'utf8_swedish_ci',
`display` TINYINT(1) UNSIGNED NOT NULL,
`search` TINYINT(1) UNSIGNED NOT NULL,
`group_id` SMALLINT(6) UNSIGNED NULL DEFAULT NULL,
UNIQUE INDEX `user_id` (`field`, `db`, `user_id`),
INDEX `Views_ibfk_1` (`user_id`),
INDEX `group_id` (`group_id`),
CONSTRAINT `Views_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `User` (`id`) ON
UPDATE CASCADE ON DELETE CASCADE
)
COLLATE='utf8_swedish_ci'
ENGINE=InnoDB;
I think you need to revise your logic. It makes no sense to Insert a row only to delete another row. Why not just update the Group_ID field in the duplicate row to what is being inserted? Below is a rough idea of how I would go about it.
N.b. I haven't done much work with MySQL and cannot get the below to run on SQLFiddle, but based on the MySQL docs I can't work out why. Perhaps someone more versed in MySQL can correct me?
SET @User_ID = 1;
SET @db = 'db_a';
SET @Field = 'Field';
SET @Display = 1;
SET @Search = 1;
SET @Group_ID = 1;
IF EXISTS
( SELECT 1
FROM Views
WHERE User_ID = @User_ID
AND DB = @DB
AND Field = @Field
AND Group_ID IS NOT NULL
)
THEN
UPDATE Views
SET Group_ID = @Group_ID,
Display = @Display,
Search = @Search
WHERE User_ID = @User_ID
AND DB = @DB
AND Field = @Field
AND Group_ID IS NOT NULL
ELSE
INSERT INTO Views (User_ID, DB, Field, Display, Search, Group_ID)
VALUES (@User_ID, @DB, @Field, @Display, @Search, @Group_ID)
END IF;
Alternatively (and my preferred solution), add a Timestamp field to your table and create a view as follows:
SELECT v.User_ID, v.DB, v.Field, v.Display, v.Search, v.Group_ID
FROM Views v
INNER JOIN
( SELECT User_ID, DB, Field, MAX(CreatedDate) AS CreatedDate
FROM Views
WHERE Group_ID IS NOT NULL
GROUP BY User_ID, DB, Field
) MaxView
ON MaxView.User_ID = v.User_ID
AND MaxView.DB = v.DB
AND MaxView.Field = v.Field
AND MaxView.CreatedDate = v.CreatedDate
WHERE v.Group_ID IS NOT NULL
UNION ALL
SELECT v.User_ID, v.DB, v.Field, v.Display, v.Search, v.Group_ID
FROM Views v
WHERE v.Group_ID IS NULL
This would allow you to track changes to your data properly, without compromising the need to be able to view unique records.
delete group_id from Views where group_id != 'NUll'
Your question is not very good to understand, so I'm not sure this is what you want:
DELETE FROM Views WHERE # delete from the table views
group_id IS NOT NULL AND # first condition delete only rows with not null group_id
(SELECT count(*) as tot FROM Views GROUP BY group_id) = 1 # second condition count the difference in group id
If that's not what you want, please update your question with more details...