#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2004
    Posts
    139
    Rep Power
    10

    How best to preserve the order of Inserts when using the 'source' command.


    If inserting into mysql via the following command, which mytable.sql file will ensure that the ORDER of the inserts (as written) will be preserved?

    The reason the order matters is because I've defined a unique key on (Column2, Column3) and know that there are some duplicates lingering in the data. I only want the FIRST occurrence to be the row that gets inserted. I'm not concerned with efficiency, just that they get inserted correctly.

    mysql> source mytable.sql;

    Example 1) mytable.sql
    PHP Code:
    INSERT INTO Table (Column1Column2Column3VALUES
    ('1''2''3'),
    (
    'A''B''C'),
    (
    'AA''BB''CC'); 

    Example 2) mytable.sql
    PHP Code:
    INSERT INTO Table (Column1Column2Column3VALUES ('1''2''3'); 
    INSERT INTO Table (Column1Column2Column3VALUES ('A''B''C'); 
    INSERT INTO Table (Column1Column2Column3VALUES ('AA''BB''CC'); 

    mysql Ver 14.14 Distrib 5.5.32, for debian-linux-gnu (i686) using readline 6.2
    Thanks,
    Dan

    Operating system ubuntu 12.04
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    use INSERT IGNORE
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2004
    Posts
    139
    Rep Power
    10
    Originally Posted by r937
    use INSERT IGNORE
    I had looked into that, but Insert Ignore would insert the last, not the first. And reversing the order would put all the other rows in reverse order, which isn't what I want.

    Does Example 2 not preserve the order? That's my main question.
    Thanks,
    Dan

    Operating system ubuntu 12.04
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    Originally Posted by dano2
    I had looked into that, but Insert Ignore would insert the last, not the first.
    not the way i understand it

    whenever a new unique key is encountered, it is inserted, and any keys with the same values encountered after that are ignored
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Problem Solver
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jan 2001
    Location
    Stockholm, Sweden
    Posts
    4,495
    Rep Power
    534
    I agree with Rudy,

    INSERT IGNORE should work for you with both your examples.

    I think you are confusing
    "INSERT IGNORE" with "INSERT ... ON DUPLICATE KEY UPDATE".
    Where the value would be overwritten each time a duplicate value is entered.

    As you can see in my simple example it takes the first occurrence for primary key id=2 and id=3 which I have duplicates for:
    Code:
    drop table testOrder;
    create table testOrder (id int primary key auto_increment, value int);
    insert ignore into testOrder values (1,1),(2,1),(3,1),(3,2),(2,2),(4,1);
    Query OK, 4 rows affected (0.00 sec)
    Records: 6  Duplicates: 2  Warnings: 0
    
    mysql> select * from testOrder;
    +----+-------+
    | id | value |
    +----+-------+
    |  1 |     1 |
    |  2 |     1 |
    |  3 |     1 |
    |  4 |     1 |
    +----+-------+
    4 rows in set (0.00 sec)
    /Stefan
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2004
    Posts
    139
    Rep Power
    10
    Thanks guys.
    Thanks,
    Dan

    Operating system ubuntu 12.04

IMN logo majestic logo threadwatch logo seochat tools logo