Wikis & learning

Modify a row selected by its stable ID

A title may change, and two rows can share the same visible name. Use a stable key to choose the row for an update or removal.

A title may change, and two rows can share the same visible name. Use a stable key to choose the row for an update or removal.

Walkthrough

Both statements here contain a WHERE condition and receive their IDs as parameters. The final query checks the persisted result. Working in memory makes this exercise independent of an existing database.

PHP example

<?php
$db = new PDO('sqlite::memory:');
$db->exec("CREATE TABLE cards(id INTEGER PRIMARY KEY, label TEXT);
INSERT INTO cards VALUES(1,'Draft'),(2,'Old')");
$db->prepare('UPDATE cards SET label=? WHERE id=?')->execute(['Published',1]);
$db->prepare('DELETE FROM cards WHERE id=?')->execute([2]);
echo implode(', ', $db->query('SELECT label FROM cards ORDER BY id')->fetchAll(PDO::FETCH_COLUMN));

Expected output

Published

Change and check

Change the delete ID to a value that does not exist. Predict both the final count and which captions will remain.