Home

MySQL REPLACE INTO

Damn handy this. Use it for data input pages where you may BOTH be adding new rows in the table or updating existing rows in the table.

TLDR

REPLACE INTO MyTable (x1, x2, x3) VALUES (@x1, @x2, @x3)

So take this table

x1 - Primary Key	x2 - Data	X3 - Data
1001	Wheelbarrow	Large
1002	Wheelbarrow	Small
1003	Spade	Large
1004	Spade	Small

We are going to ADD A NEW RECORD. This record is a Large Shovel. Because this is a NEW ITEM creating a NEW RECORD we set the index, primary key to NULL

SET @x1 = NULL;
SET @x2 = 'Shovel';
SET @x3 = 'Large';

REPLACE INTO salest(x1, x2, x3) VALUES (@x1, @x2, @x3)

MySQL goes."OK we gunna replace... errrr...replace...errr, nope no match on the primary key index so I'll add it like an insert, cool". We end up with this table... 

x1 - Primary Key	x2 - Data	X3 - Data
1001	Wheelbarrow	Large
1002	Wheelbarrow	Small
1003	Spade	Large
1004	Spade	Small
1005	Shovel	Large

But THIS time we want to update index 1003 Spade Large to be Spade Medium. As such this time we will INCLUDE the index

SET @x1 = 1003;
SET @x2 = 'Spade';
SET @x3 = 'Medium';

REPLACE INTO salest(x1, x2, x3) VALUES (@x1, @x2, @x3)

And we'll get this table

x1 - Primary Key	x2 - Data	X3 - Data
1001	Wheelbarrow	Large
1002	Wheelbarrow	Small
1003	Spade	Medium
1004	Spade	Small
1005	Shovel	Large

So when programming your code if it's an update it's got the right primary key, if it's an insert it has a null primary key.

Reader's Comments

Post Your Comment Posts/Links Rules

Name

---

Comment

---

Add a RELEVANT link (not required)

---

Upload an image (not required)
Uploading...

---

---

Home