CODEWITHSUNDEEP
mysqlbulkinsert
zato
zato

Reputation: 133

Insert or update according to any column

My problem is about bulk insert+update,

I want to insert a lot of data and update the same ones by a specific column, not by primary key at the same time. So I can't use INSERT...ON DUPLICATE KEY UPDATE.

Is there a way to do that?

For example I have a "person" table and it has "name"-"phone"-"security-number" columns. First bulk insert, I add new people, but don't want a new record that has the same name, just update its other info (phone and security-number) Another insert operation, I add new poeple, but don't want a new record that has same phone number, just update its name and security-number. (the example may be a little bit ridiculous,sorry for that)

Upvotes: 1

Views: 87

Answers (2)

Andrius Naruševičius
Andrius Naruševičius

Reputation: 8578

It is probably not the only solution, but this is what came to my mind first. Write your trigger and create it dynamically (execute sql from your code) every time you bulk insert and after the insert you delete it, so the next time you can check on different column. Pseudo-code:

CREATE TRIGGER ON INSERT
BEGIN
    DECLARE t int;
    SELECT t = COUNT(1) FROM table WHERE your_defined_column = inserted.your_defined_column
    IF(t > 0)
    BEGIN
        UPDATE table SET all_columns... WHERE your_defined_column = inserted.your_defined_column
        --STOP FROM INSERTING
    END
    ELSE
        --LET IT INSERT
    END
END

Hope this helps :)

Upvotes: 1

Lookis
Lookis

Reputation: 75

you can use unique key index instead of primary key. and the sql you insert will be the same.

just alter the table and add unique index on specify column.

Upvotes: 0

Related Questions