Rectangle 27 29

There is a MySQL specific extension to SQL that may be what you want - REPLACE INTO

However it does not work quite the same as 'ON DUPLICATE UPDATE'

It deletes the old row that clashes with the new row and then inserts the new row. So long as you don't have a primary key on the table that would be fine, but if you do, then if any other table references that primary key

You can't reference the values in the old rows so you can't do an equivalent of

INSERT INTO mytable (id, a, b, c) values ( 1, 2, 3, 4) 
ON DUPLICATE KEY UPDATE
id=1, a=2, b=3, c=c + 1;

I'd like to use the work around to get the ID to!

That should work last_insert_id() should have the correct value so long as your primary key is auto-incrementing.

However as I said, if you actually use that primary key in other tables, REPLACE INTO probably won't be acceptable to you, as it deletes the old row that clashed via the unique key.

Someone else suggested before you can reduce some typing by doing:

INSERT INTO `tableName` (`a`,`b`,`c`) VALUES (1,2,3)
ON DUPLICATE KEY UPDATE `a`=VALUES(`a`), `b`=VALUES(`b`), `c`=VALUES(`c`);

So, I can't stick with the primary key before a replace into query?

No - that row is deleted and a new row created, which will have a new primary key.

mysql - On Duplicate Key Update same as insert - Stack Overflow

mysql on-duplicate-key
Rectangle 27 53

The UPDATE statement is given so that older fields can be updated to new value. If your older values are the same as your new ones, why would you need to update it in any case?

For eg. if your columns a to g are already set as 2 to 8; there would be no need to re-update it.

INSERT INTO table (id,a,b,c,d,e,f,g)
VALUES (1,2,3,4,5,6,7,8) 
ON DUPLICATE KEY
    UPDATE a=a, b=b, c=c, d=d, e=e, f=f, g=g;

To get the id from LAST_INSERT_ID; you need to specify the backend app you're using for the same.

conn:getlastautoid()

It's just for the example. In a real life query there are differences. My question is quiet simple: Do I really need to specify all the fields (and values in my first example) if they are the same as in the insert? I just want to insert all or if there is a unique value match: update all.

Yes, you do need to update all of them in that case.

"A extra note, I'd like to use the work around to get the ID too!"

@Tresdin, As far as I can tell it is just syntactic sugar. Personally, I have never seen anybody using the a=VALUES(a), b=VALUES(b), etc. Maybe you can assign multiple values to the column? Not sure why you wouldn't just concatenate the data beforehand though.

tbl
INSERT INTO tbl(id, a) VALUES(1,2) ON DUPLICATE KEY UPDATE a=VALUES(a)
INSERT INTO tbl(id, a) VALUES(1,2) ON DUPLICATE KEY UPDATE a=a

mysql - On Duplicate Key Update same as insert - Stack Overflow

mysql on-duplicate-key
Rectangle 27 1074

INSERT ... ON DUPLICATE KEY UPDATE
INSERT INTO table (id, name, age) VALUES(1, "A", 19) ON DUPLICATE KEY UPDATE    
name="A", age=19

+1 From what I've found, this method is less problematic for auto-increment keys and other unique key collisions than REPLACE INTO, and it is more efficient.

I know all of you allude to this, but I want to be explicit for others. If the ID you insert is NOT the PRIMARY KEY or UNIQUE, then this will not work. This didn't initially work for me because my ID was not unique.

I wonder why affected row count results in 2 when successfully updating (on duplicate key) single row? Anyone else had this result? (The data is updated correctly: meaning only 1 row is updated)

This is a bit late, but anyway: it is stated in the manual that updates in ON DUPLICATE KEY UPDATE increase the affected rows by 2. It reports 0 if nothing is actually updated (same as the regular UPDATE).

Also note that you can use VALUES (name) to reference to the value you attempt to insert e.g. INSERT INTO table (id, name, age) VALUES(1, "A", 19) ON DUPLICATE KEY UPDATE name=VALUES(name) , age=VALUES(age)

sql - Insert into a MySQL table or update if exists - Stack Overflow

mysql sql insert-update
Rectangle 27 331

VALUES
INSERT INTO beautiful (name, age)
    VALUES
    ('Helen', 24),
    ('Katrina', 21),
    ('Samia', 22),
    ('Hui Ling', 25),
    ('Yumie', 29)
ON DUPLICATE KEY UPDATE
    age = VALUES(age),
     ...

Thanks a ton. That's exactly what I want.... Thanks again...

What if we need to include an IF statement in the UPDATE that has a different condition for each row?

@logic: There are several questions related to this: StackOverflow search. If none of them answers your question, please post a new one explaining exactly what you need.

sql - MySQL ON DUPLICATE KEY UPDATE for multiple rows insert in single...

sql mysql duplicates
Rectangle 27 315

VALUES
INSERT INTO beautiful (name, age)
    VALUES
    ('Helen', 24),
    ('Katrina', 21),
    ('Samia', 22),
    ('Hui Ling', 25),
    ('Yumie', 29)
ON DUPLICATE KEY UPDATE
    age = VALUES(age),
     ...

Thanks a ton. That's exactly what I want.... Thanks again...

What if we need to include an IF statement in the UPDATE that has a different condition for each row?

@logic: There are several questions related to this: StackOverflow search. If none of them answers your question, please post a new one explaining exactly what you need.

sql - MySQL ON DUPLICATE KEY UPDATE for multiple rows insert in single...

sql mysql duplicates
Rectangle 27 184

INSERT ... ON DUPLICATE KEY UPDATE
INSERT INTO `usage`
(`thing_id`, `times_used`, `first_time_used`)
VALUES
(4815162342, 1, NOW())
ON DUPLICATE KEY UPDATE
`times_used` = `times_used` + 1

Yeah, I believe SQL Server's equivalent is called MERGE. In general, the concept is often referred to as "UPSERT".

Make a unique index on GEB and Topic

geb
topic
ALTER TABLE table ADD UNIQUE geb_by_topic (geb, topic)

@Brian: Oracle's equivalent is also called MERGE but I'm not sure if its syntax is identical to SQL Server's.

VALUES(col)
ON DUPLICATE UPDATE b = VALUES(b), c = VALUES(c)

sql - How do I update if exists, insert if not (AKA "upsert" or "merge...

sql mysql insert-update upsert
Rectangle 27 184

INSERT ... ON DUPLICATE KEY UPDATE
INSERT INTO `usage`
(`thing_id`, `times_used`, `first_time_used`)
VALUES
(4815162342, 1, NOW())
ON DUPLICATE KEY UPDATE
`times_used` = `times_used` + 1

Yeah, I believe SQL Server's equivalent is called MERGE. In general, the concept is often referred to as "UPSERT".

Make a unique index on GEB and Topic

geb
topic
ALTER TABLE table ADD UNIQUE geb_by_topic (geb, topic)

@Brian: Oracle's equivalent is also called MERGE but I'm not sure if its syntax is identical to SQL Server's.

VALUES(col)
ON DUPLICATE UPDATE b = VALUES(b), c = VALUES(c)

sql - How do I update if exists, insert if not (AKA "upsert" or "merge...

sql mysql insert-update upsert
Rectangle 27 184

INSERT ... ON DUPLICATE KEY UPDATE
INSERT INTO `usage`
(`thing_id`, `times_used`, `first_time_used`)
VALUES
(4815162342, 1, NOW())
ON DUPLICATE KEY UPDATE
`times_used` = `times_used` + 1

Yeah, I believe SQL Server's equivalent is called MERGE. In general, the concept is often referred to as "UPSERT".

Make a unique index on GEB and Topic

geb
topic
ALTER TABLE table ADD UNIQUE geb_by_topic (geb, topic)

@Brian: Oracle's equivalent is also called MERGE but I'm not sure if its syntax is identical to SQL Server's.

VALUES(col)
ON DUPLICATE UPDATE b = VALUES(b), c = VALUES(c)

sql - How do I update if exists, insert if not (AKA "upsert" or "merge...

sql mysql insert-update upsert
Rectangle 27 7

ON DUPLICATE KEY UPDATE is not really in the standard. It's about as standard as REPLACE is. See SQL MERGE.

Essentially both commands are alternative-syntax versions of standard commands.

replace does a delete and insert, whereas onduplicate key update updates the existing row. some differences are: auto incrementing id, row position, a bunch of triggers

mysql - "INSERT IGNORE" vs "INSERT ... ON DUPLICATE KEY UPDATE" - Stac...

mysql insert
Rectangle 27 7

ON DUPLICATE KEY UPDATE is not really in the standard. It's about as standard as REPLACE is. See SQL MERGE.

Essentially both commands are alternative-syntax versions of standard commands.

replace does a delete and insert, whereas onduplicate key update updates the existing row. some differences are: auto incrementing id, row position, a bunch of triggers

mysql - "INSERT IGNORE" vs "INSERT ... ON DUPLICATE KEY UPDATE" - Stac...

mysql insert
Rectangle 27 7

ON DUPLICATE KEY UPDATE is not really in the standard. It's about as standard as REPLACE is. See SQL MERGE.

Essentially both commands are alternative-syntax versions of standard commands.

replace does a delete and insert, whereas onduplicate key update updates the existing row. some differences are: auto incrementing id, row position, a bunch of triggers

mysql - "INSERT IGNORE" vs "INSERT ... ON DUPLICATE KEY UPDATE" - Stac...

mysql insert
Rectangle 27 12

on duplicate key update
MERGE
MERGE INTO my_table trg  
USING (SELECT 30 as question_id,0 as ug FROM DUAL
UNION ALL 
SELECT 31,1 FROM DUAL) src ON (src.question_id = trg.question_id)
WHEN NOT MATCHED THEN INSERT(question_id, ug) VALUES
(src.question_id, src.ug)
WHEN MATCHED THEN UPDATE
SET trg.ug = src.ug

@mauek unak: Oracle database doesn't have ON DUPLICATE KEY UPDATE. The question is about Oracle database, not about Mysql which is owned by Oracle corp. Documentation you provided is about Mysql 5.1 . :)

sql - Oracle: ON DUPLICATE KEY UPDATE - Stack Overflow

sql oracle upsert
Rectangle 27 12

on duplicate key update
MERGE
MERGE INTO my_table trg  
USING (SELECT 30 as question_id,0 as ug FROM DUAL
UNION ALL 
SELECT 31,1 FROM DUAL) src ON (src.question_id = trg.question_id)
WHEN NOT MATCHED THEN INSERT(question_id, ug) VALUES
(src.question_id, src.ug)
WHEN MATCHED THEN UPDATE
SET trg.ug = src.ug

@mauek unak: Oracle database doesn't have ON DUPLICATE KEY UPDATE. The question is about Oracle database, not about Mysql which is owned by Oracle corp. Documentation you provided is about Mysql 5.1 . :)

sql - Oracle: ON DUPLICATE KEY UPDATE - Stack Overflow

sql oracle upsert
Rectangle 27 528

Yes, that's possible - you can use INSERT ... ON DUPLICATE KEY UPDATE.

INSERT INTO table (id,Col1,Col2) VALUES (1,1,1),(2,2,3),(3,9,3),(4,10,12)
ON DUPLICATE KEY UPDATE Col1=VALUES(Col1),Col2=VALUES(Col2);

If there is no duplicates then i dont want that row to be inserted. what should id do? because i am fetching information from another site which maintains tables with id's. I am inserting values with respect to that id. if the site has new records then i will end up inserting only the ids and count except all other information. if and only if there is an entry for the id then it should update else it should skip. what shall i do?

@JayapalChandran you should use INSERT IGNORE together with ON DUPLICATE KEY UPDATE. dev.mysql.com/doc/refman/5.5/en/insert.html

@HaralanDobrev Using INSERT IGNORE still inserts the non duplicated records. which Jayapal wanted to avoid. INSERT IGNORE just turns any errors into warning :( stackoverflow.com/questions/548541/

sql - Multiple Updates in MySQL - Stack Overflow

mysql sql sql-update
Rectangle 27 511

Yes, that's possible - you can use INSERT ... ON DUPLICATE KEY UPDATE.

INSERT INTO table (id,Col1,Col2) VALUES (1,1,1),(2,2,3),(3,9,3),(4,10,12)
ON DUPLICATE KEY UPDATE Col1=VALUES(Col1),Col2=VALUES(Col2);

If there is no duplicates then i dont want that row to be inserted. what should id do? because i am fetching information from another site which maintains tables with id's. I am inserting values with respect to that id. if the site has new records then i will end up inserting only the ids and count except all other information. if and only if there is an entry for the id then it should update else it should skip. what shall i do?

@JayapalChandran you should use INSERT IGNORE together with ON DUPLICATE KEY UPDATE. dev.mysql.com/doc/refman/5.5/en/insert.html

@HaralanDobrev Using INSERT IGNORE still inserts the non duplicated records. which Jayapal wanted to avoid. INSERT IGNORE just turns any errors into warning :( stackoverflow.com/questions/548541/

sql - Multiple Updates in MySQL - Stack Overflow

mysql sql sql-update
Rectangle 27 3

You can only make alterations in the context of one conflicting row in the ON DUPLICATE KEY area. Further, this is, as far as I know, a property of the INSERT statement.

What you need is a simple ledger where you record the additions and subtractions from a balance, then tabulate those either manually or using triggers.

For instance, the simplest approach is:

This might be more easily represented as a pair of entries:

INSERT INTO points_adjustments (boardId, points)
  VALUES (?, ?)

You'd add one entry for +n points, and a matching one for -n. At any time you can get a balance using SUM(points). You could wrap this up in a VIEW to make retrieval easier, or if you want, denormalize the sums into a column of another table using a trigger.

A simple trigger would issue the following statement for each affected boardId:

INSERT INTO balances (boardId, points) VALUES (?, ?)
  ON DUPLICATE KEY SET points=points+VALUES(points)

This avoids key collisions in the first place and provides an auditable record of the transactions that occurred.

In any case, to do all of this automatically you'd probably have to use a trigger.

I like this solution, but I never used triggers before I guess it's time for me to do so. As about escaping... $from = (int)mysql_real_escape_string($_REQUEST['from']); $to = (int)mysql_real_escape_string($_REQUEST['to']); $delete = $condition[mysql_real_escape_string($_REQUEST['delete'])]; $contest = $condition[mysql_real_escape_string($_REQUEST['contest'])]; is there anything more I could do?

Yes. Triggers would be the logical next step. Automated deletes on constraint violations was an ambitious thought :)

Any use of mysql_real_escape_string in the year 2012 is terrifying. Please, for your own sanity use something sensible like PDO. With SQL placeholders this would literally collapse to two easy, readable lines. If you use ? or, better, named parameters, then you're just a bindParam call or two away from safety. It takes all of thirty minutes to learn how to use PDO effectively.

This is also the "Help, I'm having trouble with my stone arrow, how to spear mastadon" type of answer. There are many good ORM database layer packages for PHP that would make this kind of database work considerably easier. Making low-level SQL calls and hand-writing queries is not fun and not very productive.

mysql - update on duplicate key update - Stack Overflow

mysql sql-update on-duplicate-key
Rectangle 27 1

INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6)
  ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);

Seems like the original colum is available under its name, and the values to be inserted need the VALUES() function. Updating to the max of new and old price should be easy: How to get the max of two values in MySQL?

Update to the MIN of both prices then.

I am not clear i think i have tried everything regarding this i just dont get it :-) $sql = mysql_query("INSERT INTO varer (sku,productname,price,mpn,stock,distributor) VALUES ('$sku','$productname','$price','$mpn','$stock','$distributor')"); *** IF it find same $sku in database, it should check if price for that sku in the database is lower than the price from this csv file. if price is lower it should update price, productname, mpn, etc from the csv file. Let me know how you would do it in this example

php - ON DUPLICATE KEY UPDATE with if statement - Stack Overflow

php mysql duplicates
Rectangle 27 13

INSERT INTO table (id,name,age) VALUES('1','Mohammad','21') ON DUPLICATE KEY UPDATE name='Mohammad',age='21'

Note: Here if id is the primary key then after first insertion with id='1' every time attempt to insert id='1' will update name and age and previous name age will change.

@ersks see the question. user asked about when there is an unique key

I got that, but I am trying to solve my problem in which these only values are know. So, in such situation I wrote above comment hoping correct solution.

sql - Insert into a MySQL table or update if exists - Stack Overflow

mysql sql insert-update
Rectangle 27 144

create table tmp like yourtable;

alter table tmp add unique (text1, text2);

insert into tmp select * from yourtable 
    on duplicate key update text3=ifnull(text3, values(text3));

rename table yourtable to deleteme, tmp to yourtable;

drop table deleteme;

Should be much faster than anything that requires group by or distinct or a subquery, or even order by. This doesn't even require a filesort, which is going to kill performance on a large temporary table. Will still require a full scan over the original table, but there's no avoiding that.

Thanks, it works! 1.2 mil rows became 0.6 mil in 60 minutes, so that's around 10000 rows written per minute. Thanks for the clear explanation too! :)

@u (upsideDownNick) simple and effective. for those who do not care for the text3 not null part, you can use INSERT IGNORE (disconsidering the ON DUPLICATE UPDATE part) and mysql will ignore errors and insert only the first distinct value it finds (ignoring subsequent duplicates).

+1 This is a smart solution. In my case, the client loses connection to server about 10 minutes in (table has 45+ million records) and results in messy locks being open, etc. -- any recommendation for how to handle this?

In case anyone is interested, I expanded the @u response with FURTHER USE CASES on stackoverflow.com/questions/3311903/

sql - MySQL remove duplicates from big database quick - Stack Overflow

sql mysql duplicates
Rectangle 27 43

INSERT INTO table (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE c=c+1;

UPDATE table SET c=c+1 WHERE a=1;

Example of insert ignore based on mysql.com

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name [(col_name,...)]
    {VALUES | VALUE} ({expr | DEFAULT},...),(...),...
    [ ON DUPLICATE KEY UPDATE
      col_name=expr
        [, col_name=expr] ... ]
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name
    SET col_name={expr | DEFAULT}, ...
    [ ON DUPLICATE KEY UPDATE
      col_name=expr
        [, col_name=expr] ... ]
INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name [(col_name,...)]
    SELECT ...
    [ ON DUPLICATE KEY UPDATE
      col_name=expr
        [, col_name=expr] ... ]

php - How to 'insert if not exists' in MySQL? - Stack Overflow

php sql mysql performance primary-key