Inserting Data - Learning MySQL and MariaDB - page 129

code) will replace the matching row already in the table. This can be very useful, and not
difficult. Let's look at an example:
REPLACE INTO bird_families
( scientific_name , brief_description , order_id )
VALUES ( 'Viduidae' , 'Indigobirds & Whydahs' , 128 ),
( 'Estrildidae' , 'Waxbills, Weaver Finches, & Allies' , 128 ),
( 'Ploceidae' , 'Weavers, Malimbe, & Bishops' , 128 );
Query OK , 6 rows affected ( 0 . 39 sec )
Records : 3 Duplicates : 3 Warnings : 0
Notice that the syntax is the same as an INSERT statement. The options all have the same
effect as well. Also, multiple rows may be inserted, but there's no need for the IGNORE
option because duplicates are just overwritten.
Actually, when a row is replaced using the REPLACE statement, it's first deleted com-
pletely and the new row is then inserted. For any columns without values, the default val-
ues for the columns will be used. None of the previous values are kept. So be careful that
you don't replace a row that contains some data that you want. When you update a row
with REPLACE , you can't choose to replace some columns and leave the others un-
changed. REPLACE replaces the whole row,unlike UPDATE . To change just specific
columns, use the UPDATE statement.
There are a couple of things that you should notice about this REPLACE statement and the
content we entered. You can see something unusual in the results message. It says that six
rows were affected by this SQL statement: three new records and three duplicates. The
value of six for the number of rows affected may seem strange. What happened is that be-
cause three rows had the same value for the scientific_name , they were deleted.
And then three new rows were added with the new values, the replacements. That gives a
total of six affected rows: three deleted and three added.
The results contain no warnings, so all went well as far as MySQL knows. Let's look at
the data for one of the bird families we changed in the bird_families table, the
Viduidae family:
SELECT * FROM bird_families
WHERE scientific_name = 'Viduidae' \G
*************************** 1. row ***************************
family_id: 331
scientific_name: Viduidae
brief_description: Indigobirds & Whydahs
order_id: 128