CODEWITHSUNDEEP
mysql
Murali
Murali

Reputation: 87

How to use MySql 'Insert Ignore into' query to insert records based on some values from a different table

I have two tables as below. The Id column value in both the tables mentioned below is auto-incremented.

LoginInfo
+----+------------+-------------+
| Id | LoginName | IP |
+------+----------+-------------+
| 1 | User1 | aa.bb.cc.dd|
| 2 | User2 | xx.xx.xx.xx|
| 3 | User3 | ii.ii.ii.ii|
+----+------------+-------------+

UserGroups
+----+------------+-------------+
| Id | LoginName | Groups |
+------+----------+-------------+
| 1 | User1 | Group1 |
| 2 | User1 | Default |
| 3 | User2 | Group2 |
| 4 | User3 | Group3 |
+----+------------+-------------+

Each of the user in LoginInfo table should be a member of 'Default' group in UserGroups table. So in the above table User1 is associated to 'Default' group but User2 and User3 are not.

I would like to check whether 'Default' group entry is added for each of the User in UserGroups table. If available Ignore Insert, else Insert a new record for that User.

The mysql query which I tried is
INSERT IGNORE INTO USERGROUPS(LOGINNAME, GROUPS) SELECT LOGINNAME, 'Default' FROM LOGININFO L WHERE L.IP IS NOT NULL;
After execution, the message displayed as 3 rows affected and the UserGroups table look like

+----+------------+-------------+
| Id | LoginName | Groups |
+------+----------+-------------+
| 1 | User1 | Group1 |
| 2 | User1 | Default |
| 3 | User2 | Group2 |
| 4 | User3 | Group3 |
| 5 | User1 | Default |
| 6 | User2 | Default |
| 7 | User3 | Default |
+----+------------+-------------+

But what I actually look for is as below

+----+------------+-------------+
| Id | LoginName | Groups |
+------+----------+-------------+
| 1 | User1 | Group1 |
| 2 | User1 | Default |
| 3 | User2 | Group2 |
| 4 | User3 | Group3 |
| 5 | User2 | Default |
| 6 | User3 | Default |
+----+------------+-------------+

Please let know suggestions on the query or any workarounds.

Upvotes: 1

Views: 52

Answers (2)

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

You can form the SELECT part of the INSERT statement in a way that duplicate records are filtered out:

INSERT INTO UserGroups(LoginName, Groups) 
SELECT LoginName, 'Default' 
FROM LoginInfo AS l 
WHERE l.IP IS NOT NULL 
      AND NOT EXISTS (SELECT 1
                      FROM UserGroups AS ug
                      WHERE ug.LoginName = l.LoginName AND ug.Groups = 'Default');

You then don't need to use IGNORE.

Demo here

Upvotes: 1

fthiella
fthiella

Reputation: 49049

You have to add an unique constraint before executing INSERT IGNORE:

ALTER TABLE UserGroups ADD UNIQUE (LoginName, Groups)

then you can execute this query:

INSERT IGNORE INTO USERGROUPS(LOGINNAME, GROUPS)
SELECT LOGINNAME, 'Default'
FROM LOGININFO
WHERE IP IS NOT NULL

Please see this fiddle.

Upvotes: 2

Related Questions