MYSQL multiple row INSERT WHERE NOT EXISTS

Question

I've been looking everywhere and can't find anything like this. I have two tables, table one t1 has 12 entries for each a1 value, b1 values are static for all a1 except one entry. neither a1 or b1 are primary key. t1 also has other static columns for all a1.

table two t2 has a2 and the b2 value that is not static. neither a2 nor b2 are primary keys (I use independent id columns for primary keys).

my goal is to import all a2 values that do not exist in t1. keep in mind table 1 has ~2 million entries currently, and table 2 will generally have 150K-1M entries.

this works:

INSERT t1 ( a1, b1, c1, d1 )
SELECT a2, b2, 'constant1', 'constant2'
FROM t2 AS V2
WHERE NOT EXISTS ( SELECT 1 FROM t1 WHERE a1=V2.a2 )

but it only enters one a2 entry into t1, leaving 11 missing entries.

This is my idea:

SELECT a2, b2 FROM t2 AS V2
INSERT INTO t1 ( a1, b1, c1, d1 )
       VALUES  ( V2.a2, V2.b2, 'constant1', 'constant2' ),
               ( V2.a2, 'constant3', 'constant1', 'constant2'),
               ( V2.a2, 'constant4', 'constant1', 'constant2'),
               ( V2.a2, 'constant5', 'constant1', 'constant2'),
               ( V2.a2, 'constant6', 'constant1', 'constant2'),
               ( V2.a2, 'constant7', 'constant1', 'constant2'),
               ( V2.a2, 'constant8', 'constant1', 'constant2'),
               ( V2.a2, 'constant9', 'constant1', 'constant2'),
               ( V2.a2, 'constant10', 'constant1', 'constant2'),
               ( V2.a2, 'constant11', 'constant1', 'constant2'),
               ( V2.a2, 'constant12', 'constant1', 'constant2'),
               ( V2.a2, 'constant13', 'constant1', 'constant2')
WHERE NOT EXISTS ( SELECT 1 FROM t1 WHERE a1=V2.a2 )

Is something like this possible? (This obviously doesn't work, major syntax errors).

SAMPLE DATA FROM ACTUAL TABLE:

 +---------+------+-------------+-------------+--------------+
 | id      | name | destination | application | durationRate |
 +---------+------+-------------+-------------+--------------+
 |   45013 | 1    | 1305200     | audio       | 0            | 
 |  335203 | 2    | 1305200     | audio       | 0.014        | 
 |  625393 | 3    | 1305200     | audio       | 0.005928     | 
 |  956657 | 4    | 1305200     | audio       | 0            | 
 | 1251735 | 5    | 1305200     | audio       | 0            | 
 | 1560365 | 6    | 1305200     | audio       | 0.02         | 
 | 1868995 | 7    | 1305200     | audio       | 0.014        | 
 | 2200259 | 9    | 1305200     | audio       | 0.014        | 
 | 2517971 | 10   | 1305200     | audio       | 0.018        | 
 | 2813049 | 11   | 1305200     | audio       | 0.018        | 
 | 3121679 | 12   | 1305200     | audio       | 0.00775      | 
 | 3430309 | 8    | 1305200     | audio       | 0            | 
 |   45015 | 1    | 1305201     | audio       | 0            | 
 |  335205 | 2    | 1305201     | audio       | 0.014        | 
 |  625395 | 3    | 1305201     | audio       | 0.006552     | 
 |  956659 | 4    | 1305201     | audio       | 0            | 
 | 1251737 | 5    | 1305201     | audio       | 0            | 
 | 1560367 | 6    | 1305201     | audio       | 0.02         | 
 | 1868997 | 7    | 1305201     | audio       | 0.014        | 
 | 2200261 | 9    | 1305201     | audio       | 0.014        | 
 | 2517973 | 10   | 1305201     | audio       | 0.018        | 
 | 2813051 | 11   | 1305201     | audio       | 0.018        | 
 | 3121681 | 12   | 1305201     | audio       | 0.00775      | 
 | 3430311 | 8    | 1305201     | audio       | 0            | 
 |   45017 | 1    | 1305202     | audio       | 0            | 
 |  335207 | 2    | 1305202     | audio       | 0.014        | 
 |  625397 | 3    | 1305202     | audio       | 0.00468      | 
 |  956661 | 4    | 1305202     | audio       | 0            | 
 | 1251739 | 5    | 1305202     | audio       | 0            | 
 | 1560369 | 6    | 1305202     | audio       | 0.02         | 
 | 1868999 | 7    | 1305202     | audio       | 0.014        | 
 | 2200263 | 9    | 1305202     | audio       | 0.014        | 
 | 2517975 | 10   | 1305202     | audio       | 0.018        | 
 | 2813053 | 11   | 1305202     | audio       | 0.018        | 
 | 3121683 | 12   | 1305202     | audio       | 0.00775      | 
 | 3430313 | 8    | 1305202     | audio       | 0            | 
 +---------+------+-------------+-------------+--------------+
 36 rows in set (0.00 sec)

In my example table, a1=destination, b1=durationRate, c1=name, d1=application There 12 different rate decks defined by name.

Why are you expecting it to insert 12 rows from t2 to t1? Perhaps it would be clearer if you show a small amount of sample data from each of t1 and t2 and therefore which rows you expect to get inserted into t1? — kaj
– kaj, Commented Apr 6, 2012 at 6:50

Michał Powaga · Accepted Answer · 2012-04-06 05:40:54Z

2

Use more specific condition in where clause. I've added here another column but it's going to work only id pair a1 and b1 is unique, if it's not you should add more columns to condition.

INSERT t1 ( a1, b1, c1, d1 )
SELECT a2, b2, 'constant1', 'constant2'
FROM t2 AS V2
WHERE NOT EXISTS (
    SELECT 1 
    FROM t1 WHERE a1 = V2.a2 and b1 = V2.b2
)

answered Apr 6, 2012 at 5:40

Michał Powaga

23.4k8 gold badges55 silver badges62 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

necival Over a year ago

So, first I should have a command populate t2 with the other 11 entries for each a2?

Michał Powaga Over a year ago

@necival, no, not exactly. Which columns do you want to be unique in t1 table? All or only some of them?

necival Over a year ago

There is one entry to be made in t1 with t1.a1=t2.a2 and t1.a1=t2.b2 values, and 11 entries with t1.a1=t2.a2 and different but constant values in the b1 column.

kaj · Accepted Answer · 2012-04-10 07:57:23Z

Ok, I think I understand what you want now.

You have 11 rows to be created for each new a2 value that should be populated with the same values that others of the same name column (c1) value would have (side comment - isn't this an opportunity for normalising the tables and reducing the amount of data stored?). Then the twelfth row for a given a2 value should have the unique value for b1 as specified in t2.

One way to create this is first to populate a temporary table with the standard 11 values. Then use that plus the entries in t2 to get the rows to insert

Using SQL Server syntax:

insert into @staticValues (c1, otherCol, b1)  
select c1, otherCol, b1  
from t1  
where a1 = 1305202 -- arbitrary sample existing value  
and c1 <> 3 -- ignoring the varying row

insert into t1 (a1, c1, otherCol, b1)  
select t2.a2, sv.c1, sv.otherCol, sv.b1  
from @staticValues sv  
  inner join t2 on t2.a2 = t2.a2 -- equivalent to a cross join  
where not exists (select 1 from t1 where t1.a1 = t2.a2)  
union  
select t2.a2, 3, 'audio', t2.b2  
from t2  
where not exists (select 1 from t1 where t1.a1 = t2.a2)

Collectives™ on Stack Overflow

MYSQL multiple row INSERT WHERE NOT EXISTS

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related