On Mar 10, 2011, at 8:05 PM, Peter Sharp wrote:
$query = $this->getDbTable()->getAdapter()->query($sql, $data);
The above is how I would do it. Just form the complex UPDATE statement manually as a string, and pass it to query().
MySQL's multi-table UPDATE syntax is nonstandard SQL, and the stock Zend_Db update() methods don't support it.
The purpose of a frameworks is to make it quicker to code common tasks, not to provide a new interface for every imaginable corner case. It's normal and expected to find it necessary to "bypass" the framework when you have uncommon cases like your multi-table UPDATE.
$query->execute();
Be aware that query() internally does a prepare() and execute(), so running execute() a second time is unnecessary. Also, unless your UPDATE is idempotent, you could change your data in ways you don't intend. If you had interpolated the values into the SQL string and executed it twice, it would swap 21 and 22 and then by executing again, it would swap them back to their original rows! :-)
But in your example, the second call to execute() should fail anyway, because the query expects parameters but you aren't passing any.
Regards, Bill Karwin
