CODEWITHSUNDEEP
mysqlsql
hellzone
hellzone

Reputation: 5246

Is there a generic way to 'insert if not exists' in MySQL?

I have a db dump and its so huge(nearly 150gb). I want to insert this dump data to another schema which also has data. There are some identical rows and they gives Duplicate entry error when I try to import this dump. Is there a way to "insert if not exists while importing this dump"?

Upvotes: 0

Views: 1318

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271111

You can use insert on duplicate key update:

insert into table1(id, col1, . . .)
     select id, col1, col2, . . 
     from table2
     on duplicate key update id = values(id);

For this to work, you need a unique index (or constraint) on the id column(s). Duplicates in this column are not allowed.

The on duplicate key part says to update the record when a duplicate is found. The id = values(id) is a no-op. The id is set to itself, so it does nothing and the effect of the statement is just to insert rows that are not in the original table.

Upvotes: 1

Ionic
Ionic

Reputation: 3935

Well if I get you right - yes.

You can use EXCEPT in the INSERT commands.

See this example:

INSERT INTO yourTargetTable(name)
    SELECT name
    FROM yourSourceTable
    EXCEPT
    SELECT name
    FROM yourTargetTable

Upvotes: 0

Related Questions