CODEWITHSUNDEEP
mysqlsqlreplace
jcm
jcm

Reputation: 5659

REPLACE versus INSERT in SQL

I am doing the following SQL tutorial: http://sql.learncodethehardway.org/book/ex11.html

and in this exercise the author says in the second paragraph:

In this situation, I want to replace my record with another guy but keep the unique id. Problem is I'd have to either do a DELETE/INSERT in a transaction to make it atomic, or I'd need to do a full UPDATE.

Could anyone explain to me what the problem is with doing an UPDATE, and when we might choose REPLACE instead of UPDATE?

The UPDATE code:

UPDATE person SET first_name = "Frank", last_name = "Smith", age = 100
    WHERE id = 0;

Here is the REPLACE code:

REPLACE INTO person (id, first_name, last_name, age)
    VALUES (0, 'Frank', 'Smith', 100);

EDIT: I guess another question I have is why would you ever do a DELETE/INSERT instead of just an UPDATE as is discussed in the quoted section?

Upvotes: 31

Views: 78592

Answers (5)

data science
data science

Reputation: 1

How the REPLACE INTO statement works:

AS INSERT:

REPLACE INTO table_name (column1name, column2name, ...) 
VALUES (value1, value2, ...);

AS UPDATE:

REPLACE INTO table_name SET column1name = value, column2name = value, ... ;

The REPLACE statement checks whether the intended data record's unique key value already exists in the table before inserting it as a new record or updating it.

The REPLACE INTO statement attempts to insert a new record or modify an existing record. In both cases, it checks whether the unique key of the proposed record already exists in the table. Suppose a value of NO or FALSE is returne. In that case, the REPLACE statement inserts the record similar to the INSERT INTO statement.

Suppose the key value already exists in the table (in other words, a duplicate key). In that case, the REPLACE statement deletes the existing record of data and replaces it with a new record of data. This happens regardless of whether you use the first or the second REPLACE statement syntax.

Once the REPLACE INTO statement is used to insert or modify data, it determines first whether the new data record already exists in the table. It checks if the PRIMARY or the UNIQUE KEY matches one of the existing records.

If there is no matching key, the REPLACE works like a normal INSERT statement. Otherwise, it deletes the existing record and replaces it with the new one. This is considered a sort of modification or update of an existing record. However, it would be best if you were careful here. Suppose you do not specify a value for a column in the SET clause. In that case, the REPLACE statement uses the default value (if a default value has been set). Otherwise, it's set as NULL.

Upvotes: 0

Rajen Raiyarela
Rajen Raiyarela

Reputation: 5636

Update will change the existing records value in table based on particular condition. So you can change one or many records in single query.

Insert or Replace will insert a new record if records is not present in table else will replace. Replace will only work if and only if you provide the primary key value in the insert or replace query. If you forget to add primary key field value than a new record will created in table.

Case example:-

Update: You have a calculation of wages to be done based on a formula using the column values. In this case you will always use update query as using one single query you can update multiple records.

Insert or Replace: Already mentioned in the link you shared.

Upvotes: 2

Patrick Hofman
Patrick Hofman

Reputation: 156998

According to the documentation, the difference is:

REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted.

So what it does:

  • Try to match the row using one of the available indexes;
  • If the row doesn't exist already: add a new one;
  • If the row exists already: delete the existing row and add a new one afterwards.

When might using this become useful over separate insert and update statements?

  • You can safely call this, and you don't have to worry about existing rows (one statement vs. two);
  • If you want related data to be removed when inserting / updating, you can use replace: it deletes all related data too);
  • When triggers need to fire, and you expect an insert (bad reason, okay).

Upvotes: 34

Stanislovas Kalašnikovas
Stanislovas Kalašnikovas

Reputation: 9063

UPDATE will have no effect if the row does not exist.

Where as the INSERT or REPLACE will insert if the row doesn't exists or replace the values if it does.

Upvotes: 3

FooLman
FooLman

Reputation: 457

First Replace isn't widely understood in all database engines.

Second replace inserts/updates a record based on the primary key. While with update you can specify more elaborate conditions:

UPDATE person SET first_name = 'old ' + first_name WHERE age > 50

Also UPDATE won't create records.

Upvotes: 7

Related Questions