propelorm/Propel — Issue #218: UPDATE fails with invalid SQL when no columns change
Author: abrobston
Created: 2011-12-06 06:51:22+00:00
State: open

The following fails.

```PHP

$conn = Propel::getConnection(UserPeer::DATABASE_NAME);
try
{
$conn->beginTransaction();
$user = UserPeer::retrieveByPk(trim($_POST['UserId']));
if ($user === NULL)
{
throw new Exception('User not found.');
}
if ($_POST['Password1'] != '')
{
$salt = PasswordUtil::GenerateSalt();
$user->setSalt($salt);
$user->setPassword(PasswordUtil::HashPassword($_POST['Password1'], $salt));
}
if (isset($_POST['DeactivateUser']))
{
$user->setActive(false);
}
else
{
$user->setActive(true);
}
$user->setFirstName(trim($_POST['FirstName']));
$user->setLastName(trim($_POST['LastName']));
$user->setRoleId(trim($_POST['Role']));
UserPeer::doUpdate($user, $conn);
$conn->commit();
}
catch (Exception $ex)
{
$conn->rollback();
header('HTTP/1.0 400 ' . $ex->getMessage());
session_write_close();
exit;
}
```

When the new values are the same as the old values, I get the following error:

> Unable to execute UPDATE statement [UPDATE `Users` SE WHERE Users.USERID=:p1] [wrapped: 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 'WHERE Users.USERID='&something'' at line 1]

I have checked a fix into my repository and will issue a pull request shortly. Basically, the update query gets executed with no fields in the SET clause, and in fact, some truncation gets rid of the last two characters of "SET " (the T and the space). That feature is designed to get rid of a trailing comma, no doubt, but it just highlights the problem here. My fix just skips query execution when there are no columns to update and returns 0 for the number of rows affected.