Thursday, 21 November 2013

SQL update multiple rows with a single query, different value per row


I'd like to update many rows with each row having a possibly different value, while using only a single SQL query. A query with a single WHERE clause would be inadequate as a result.


This how-to can possibly ruin a database if done wrong, so make sure that you first test it using toy data (and make sure it works properly). Use at your own risk!

Please feel free to point out any typos and mistakes in the comments below.


This is a summary (for myself) of the solution found at Karl Rixon's page here. For greater detail, please follow the aforementioned link.

Example table:

We'll use the following table for this example:

Table name: 'membership_list'

Example 1 - multiple rows, single column:

In order to make multiple updates, you can use a CASE block in SQL combined with an appropriate WHERE clause to select the appropriate rows and set the different values. For example, in order to update the column `Country` based on column `ID` alone:

UPDATE `membership_list`
  SET `Country` = CASE `ID`
    WHEN '1' THEN 'Antarctica'
    WHEN '3' THEN 'Canada'
WHERE `ID` IN (1,3);

Do NOT forget the WHERE clause otherwise all other values will be set to NULL.

Example 2 - multiple rows, multiple columns:

In order to change more than one column, more than one CASE blocks can be used. For example, in order to both the columns `Country` and `One_or_zero` based on `ID` alone:

UPDATE `membership_list`
  SET `Country` = CASE `ID`
    WHEN '1' THEN 'Antarctica'
    WHEN '3' THEN 'Canada'
      `One_or_zero` = CASE `ID`
    WHEN '1' THEN '1'
    WHEN '3' THEN '1'
WHERE `ID` IN (1,3);

Example 3 - multiple rows, multiple WHERE conditions:

Now assuming that you only want to alter all rows in `membership_list` that have '205G0' as the `S_ID` and 'Bob' as the `Name`, and we want to set all `Country` to unique values based on `One_or_zero`:

UPDATE `membership_list`
  SET `Country` = CASE `One_or_zero`
    WHEN '1' THEN 'Switzerland'
    WHEN '0' THEN 'Russia'
WHERE `One_or_zero` in (0,1) 
  AND `S_ID`='205G0'
  AND `Name`='Bob';

Example 4 - constructing a PHP query for Example 1:

Among other benefits, a single query for multiple updates becomes particularly useful when using server-side scripting such as PHP, as there is a significant gain of speed when compared to looping through many single queries.

Here is an example script to construct the query for the first example above:

$arr_id_country = array(
    '1' => 'Antarctica',
    '3' => 'Canada'

$str_ids = implode(',', array_keys($arr_id_country));

$str_when_then = "";
foreach($arr_id_country as $id => $country) {
  $str_when_then .= sprintf(" WHEN '%d' THEN '%s' ",
      $country // note, you'd sanitize this if from user input

// whitespace + appends included in example for readability
$template =   "UPDATE `membership_list` "
            . "   SET `Country` = CASE `ID` "
            . "     %s "
            . "   END "
            . " WHERE `ID` IN (%s);"

$query = sprintf($template, $str_when_then, $str_ids);

// do database queries, etc., here ...


Once again, do NOT forget to include the WHERE clause that contains all the values found in the CASE block in order to prevent accidentally affecting other rows unintentionally.


Thursday, 14 November 2013

Autorun startup script on Raspberry Pi Arch Linux


I want to run a custom script when my Raspberry Pi running Arch Linux starts up. I only need the most basic instructions.


  • You're comfortable running commands in the console
  • You know what to do if you accidentally brick your RPi
  • You know how to write shell scripts
  • You're already familiar with basic linux shell commands


These instructions were written as a reminder to myself for a fresh install of Arch Linux (2013-07-22 version) in order to run custom startup scripts. Please read through the instructions and make sure you understand all the steps first before attempting this. Results may vary, but because you're doing things as root, you might brick your RPi if you do something wrong (or if things change in different versions of Arch Linux). Follow at your own risk.

Feel free to let me know of any typos in the comments and I'll fix them right away. Best of luck!


Note that this is only one solution out of many possible ones. It may not even be the accepted correct practice, but it happened to work after lots of Googling. You can modify the instructions if you feel comfortable doing so. Also note that these instructions were written as a reminder for myself in case I need to do this again, so they may be a bit brief. This solution was tested to work with the image from the Raspberry Pi downloads page. Your results may vary with other versions (including this not working at all).


  • startup script is located at: /scripts/
  • script already has executable privileges for root
  • we want to run our script in multi-user runlevel (if this doesn't make sense, see here for details)

Step 1: create the startup service file for systemd

In this example we're making a file called "myauto.service". In practise you can name it whatever you want, so long as it doesn't replicate another service's name.

# nano /etc/systemd/system/myauto.service

Step 2: edit the .service file to contain the information needed to both run and install your service

In this example, we've included some bare-basics only which points to our startup script /scripts/

Description=Autostart custom script



Once you've edited this above file as you'd like, save it.

This file says that we want to make a service referred to as myauto.service, which should be installed at the runlevel of multi-user, that runs a one-shot command which is our startup script.

Step 3: install the service

Here we get systemctl to install the service. Remember to replace "myauto" with the name of your .service file created earlier.

# systemctl enable myauto.service

You'll see a symbolic link created in /etc/systemd/system/ that corresponds to the .service file created earlier (in this case myauto.service).

Step 4: check if service is running (after restart)

You can check if the service is running by the following command (substituting your service's name for myauto.service below):

# systemctl is-enabled myauto.service

Step 5: where do I get more information?

Check out the very helpful systemd documentation at