How to implement WordPress plugin update that modifies the database?

In short, yes the – $wpdb class. See Codex for more information.

Whenever you interact with a custom table (or any table, really) you should go through $wpdb – in particularly make sure you’re familiar with the prepare method which can help escapes queries and prevent injections.

You should be familiar with already, as you should be using it to create the table. On your install hook you should have something like:

$charset_collate="";
if ( ! empty($wpdb->charset) )
    $charset_collate = "DEFAULT CHARACTER SET $wpdb->charset";
if ( ! empty($wpdb->collate) )
    $charset_collate .= " COLLATE $wpdb->collate";

//Create custom table
$sql_custom_table ="CREATE TABLE {$wpdb->prefix}my_table (
    id bigint(20) unsigned NOT NULL auto_increment,
    column_a varchar(255) default NULL,
    column_b varchar(255) default NULL,
    PRIMARY KEY  (id)
    ) $charset_collate; ";

require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
dbDelta($sql_custom_table);

This code is actually run whenever the plug-in is activated (i.e. not just installed). So it’ll run when someone updates the plug-in automatically. Note: If they upgrade by replacing the plug-in manually – then it won’t – so you’ll need to trigger the above code on admin_init when your plug-in is upgraded (store version number in options table, check against current version).

Now you wouldn’t normally want the CREATE TABLE SQL command to be running every time you update the plug-in – this is where dBDelta() comes in.

Before running the above command – it checks if the table exists. What’s more, it checks the column types. So if the table doesn’t exist, it creates it, if it does, but some column types have changed it updates them, and if a column doesn’t exists – it adds it.

Unfortunately – if you remove a column from the above, it doesn’t automatically remove the column. To remove columns / tables you need to specifically DROP them (checking they exists before you do).