DavidSzczesniak
2/12/2018 - 2:13 PM
Replacing Data
A covenient alternative to DELETE (then INSERT), and UPDATE - when overwriting existing data. Very similar to INSERT. However, you can't insert a new row if there is an existing row in that table with the same primary key. REPLACE gets around this as it will remove any exisiting row with the same primary key and insert a new one.
\!h Simple example:
REPLACE artist VALUES (2, "Nick Cave and The Bad Seeds"); -- replace row '2' in artists with "Nick Cave and..."
\!h Alternative use with INSERT syntax:
REPLACE INTO artist VALUES (2, "Nick Cave and The Bad Seeds"); -- optional INTO keyword, for readability
REPLACE INTO artist (artist_id, artist_name) -- explicitly lists column names that the values should be inserted into
VALUES (2, "Nick Cave and The Bad Seeds"); -- INTO can be omitted
REPLACE artist SET artist_id = 2, -- uses the SET syntax, can also include INTO
artist_name = "Nick Cave and The Bad Seeds";
\!h Bulk-replace into a table (more than one row):
REPLACE artist (artist_id, artist_name)
VALUES (2, "Nick Cave and The Bad Seeds"),
(3, "Miles Dewey Davis"); -- extra row replaced
\!h If there isn't a matching row, REPLACE works just like INSERT:
REPLACE INTO artist VALUES (10, "Jane's Addiction"); -- row 10 didn't previously exist, it does now
\!h REPLACE used with SELECT
-- shuffle playlist example - suppose you've added 10 random tracks, but don't like track 7
-- replacing it with a random choice of another track:
REPLACE INTO shuffle (artist_id, album_id, track_id, sequence_id)
SELECT artist_id, album_id, track_id, 7 FROM
track ORDER BY RAND() LIMIT 1;
-- value of sequence_id kept at 7.