Can you insert ignore into table if certain fields are duplicate?-CodePudding

I am trying to insert into a MySQL table, but I came across a problem that I can't seem to solve. The problem is that I want to add a record into the table if certain fields are duplicate, but not all.

To make my problem more clear this is the table:

When I want to do an insert into this table, I want to insert ignore only if userid and status and url are duplicate. If one of those 3 are unique the record can be added into the table.

What I have tried:

INSERT IGNORE INTO mydb.mytable (unique_screen_id, userid, url, status) 
    VALUES ('1234', 1, 'something.com', 'active');

This does not give the desired result since unique_screen_id will never be duplicate and thus the statement will insert the record. I can't remove the unique_screen_id out of the query since it also needs to be added into the table

Which query can I use so that if I insert the record above, it will check if userid and status and url are duplicate, and if they are ignore the statement (and otherwise insert the statement)?

Edit:

As requested my create table query:

CREATE TABLE `screens` (
  `id` int NOT NULL AUTO_INCREMENT,
  `unique_screen_id` varchar(20) DEFAULT NULL,
  `userid` int DEFAULT NULL,
  `status` enum('active','finished') DEFAULT 'active',
  `url` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CodePudding user response：

As the user VBoka suggested the following demo displays the answer!

I needed to use a combination of the insert ignore statement and the Unique keyword!

Thanks!

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=5d19c458568ef9204c257b7ef6096eab

CodePudding user response：

I am not sure what your create table statement is like but you can add UNIQUE key:

UNIQUE (userid ,url, status)

Here is a demo

So first you create table like this(without UNIQUE KEY):

CREATE TABLE `screens2` (
  `id` int NOT NULL AUTO_INCREMENT,
  `unique_screen_id` varchar(20) DEFAULT NULL,
  `userid` int DEFAULT NULL,
  `status` enum('active','finished') DEFAULT 'active',
  `url` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

then if you add this line

INSERT IGNORE INTO screens2 ( unique_screen_id, userid, url, status) 
    VALUES ( 1, '1', 'something.com', 'active');

and then this line

INSERT IGNORE INTO screens2 ( unique_screen_id, userid, url, status) 
    VALUES ( 1, '1', 'something.com', 'finished');

and then this line

INSERT IGNORE INTO screens2 ( unique_screen_id, userid, url, status) 
    VALUES ( 2, '1', 'something.com', 'finished');

all 3 lines will be inserted...

If you create your table like this:

CREATE TABLE `screens` (
  `id` int NOT NULL AUTO_INCREMENT,
  `unique_screen_id` varchar(20) DEFAULT NULL,
  `userid` int DEFAULT NULL,
  `status` enum('active','finished') DEFAULT 'active',
  `url` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`),
  unique(userid, url, status)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

Only two of the lines will be inserted and one will be ignored. P.S. If you add UNIQUE key you will no IGNORE keyword with your insert statements.