Belajar Tidak Identik dengan Buku

INSERT INTO vs REPLACE INTO

The MySQL REPLACE statement is a MySQL extension to the SQL standard. The MySQL REPLACE statement works like the INSERT statement with the additional rules:

  1. If the record which you want to insert does not exist, the MySQL REPLACE inserts a new record.
  2. If the record which you want to insert already exists, MySQL REPLACE deletes the old record first and then insert a new record. 

In order to use MySQL REPLACE statement, you need to have at least both INSERT and DELETE privileges.
1. REPLACE

The first method is REPLACE. The syntax is the same as the INSERT function. When dealing with a record with a unique or primary key, REPLACE will either do a DELETE and then an INSERT, or just an INSERT. Now, this most likely isn’t the solution you need. This function will cause a record to be removed, and inserted at the end, which will cause the indexing to get broken apart, decreasing the efficiency of your table. If, however, you would require a different primary key with the new data, this may be your ideal solution. Here is the syntax

REPLACE INTO table(col1,col2,…) VALUES(val1,val2,…)

2. INSERT

The second method is the ON DUPLICATE KEY UPDATE clause to the INSERT function. This one actively hunts down an existing record in the table which has the same UNIQUE or PRIMARY KEY as the one we’re trying to update. If it finds an existing one, you specify a clause for which column(s) you would like to UPDATE. Otherwise, it will do a normal INSERT. Here’s the syntax:

INSERT INTO table(col1,col2,…) VALUES(val1,val2,…)


Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Happy Paperless located at Banjarnegara , Jawa Tengah, Indonesia . Reviewed by 28 customers rated: 1 / 5



Rumus-Excel.Com
Kunjungi rumus-excel.com untuk belajar excel. Anda juga dapat menyumbangkan artikelnya ke kami untuk kita publish