How can Zend_Db be used to update multiple tables using joins?

Question

Is there an equivalent to the following SQL using the Zend_Db modules?

UPDATE items,month SET items.price=month.price
WHERE items.id=month.id;

The mysql manual states

... multiple-table UPDATE statements can use any type of join permitted in SELECT statements, such as LEFT JOIN.

score 3 · Answer 1 · answered Aug 12 '11 at 16:38

3

You can always execute a query string.

$db->query("UPDATE items,month SET items.price=month.price WHERE items.id=month.id")

Disclaimer: I haven't tested it.

answered Aug 12 '11 at 16:38

Sudheer

710
6
25

score 1 · Answer 2 · answered Aug 09 '12 at 20:40

With MySQL, you can use JOIN (left, right or inner) in UPDATE the same way as you would in a SELECT statement.

Your code would look like this:

$sql = <<<END
    UPDATE table1
    INNER JOIN table2 ON table1.id = table2.other_id
    SET table1.newvalue = 'value'
    WHERE table1.id = 99
        AND table2.other_value = 'value'
END;

$result = $db->query($sql);

The $result variable will contain an object related to your adapter type. I use PDO_MYSQL so I ended up with a Zend_Db_Statement_Pdo object.

How can Zend_Db be used to update multiple tables using joins?

2 Answers2