Drupal 7 database API error "You have an error in your SQL syntax"

Question

Using the following code:

    db_update('nodesequence_nodes')
  ->fields(array(
    'order' => 1,
  ))
  ->condition('nid', 1, '=')
  ->condition('nsid', 1, '=')
  ->execute();

I get the following error:

PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'order='1' WHERE (nid = '1') AND (nsid = '1')' at line 1: UPDATE {nodesequence_nodes} SET order=:db_update_placeholder_0 WHERE (nid = :db_condition_placeholder_0) AND (nsid = :db_condition_placeholder_1) ; Array ( [:db_update_placeholder_0] => 1 [:db_condition_placeholder_0] => 1 [:db_condition_placeholder_1] => 1 ) in nodesequence_init() (line 13 of /var/www/eventbooking2/sites/all/modules/nodesequence/nodesequence.module).

I apologize I can't offer any more insight, but I hope you can.

The simple db_update code seems to me that it should work but I can't figure out why it isn't.

The database schema:

        $schema['nodesequence_nodes'] = array(
        'description' => 'Relating nodesequences to their consituent nodes.',
        'fields'     => array(
            'nsid'   => array('type' => 'int', 'unsigned' => TRUE, 'not null' => TRUE),
            'nid'    => array('type' => 'int', 'unsigned' => TRUE, 'not null' => TRUE),
            'order'  => array('type' => 'int', 'unsigned' => TRUE, 'not null' => TRUE, 'default' => 0),
        ),
        'primary key' => array('nsid', 'nid'),
    );

Answer 1

You can't use the column named 'order' in a table as this is a reserved word. You need to change it to something else.

More info here http://dev.mysql.com/doc/refman/5.5/en/reserved-words.html take a look at table Table 9.2