Difference between mysql staments
#1

I wanna know the difference between:
  • Normal insert statment
  • Insert on duplicate key statment
Reply
#2

You should first understand the definition of a key.

A key is used to identify a row in the table and/or aid with performance when sorting or grouping. The most common type of key is the primary key and every table should have one. The primary key uniquely identifies each row in the table. If you try to insert a new row with a value for the primary key that already exists you will get an appropriately named "duplicate key" error. The "on duplicate key" clause catches that error and instead opts to update some values in the row that already exists.
Reply
#3

So instead from inserting new row it updates the one which have the same data?
Reply
#4

Quote:
Originally Posted by nezo2001
View Post
So instead from inserting new row it updates the one which have the same data?
No it won't do anything if a key clashes, it returns an error. If you want to update use "UPDATE".
Reply
#5

Quote:
Originally Posted by Crayder
View Post
No it won't do anything if a key clashes, it returns an error. If you want to update use "UPDATE".
So what is the difference if both give errors?
Reply
#6

....
Quote:
Originally Posted by Vince
Code:
INSERT INTO ... ON DUPLICATE KEY UPDATE ...
This query will try to insert the data as normal. If this fails because the data already exists (duplicate key) it will instead perform an update.
Reply
#7

Use update statements if you wish to update
Reply
#8

I'm asking for the differnece between
Insert into
Insert into duplicate key
Reply
#9

Quote:
Originally Posted by nezo2001
View Post
So instead from inserting new row it updates the one which have the same data?
Yes. You can even check this afterwards: cache_affected_rows will return 1 if a new row was inserted or 2 if an existing row was updated.
Reply
#10

Thank you vince
Reply


Forum Jump:


Users browsing this thread: 1 Guest(s)