UPDATE on SELECT With MySQL

Written on July 14, 2009 by Zaki and No one have comment

mysql
Previously if there a need to something like this, I would have definitely create a temporary tool using PHP to automatically loop a SELECT and UPDATE each field inside the loop, for example:

$sql = "SELECT * FROM item";
$query = $this->db->query($sql);
foreach( $query->result_array() as $row ) :
    $this->db->query("UPDATE relationship
        SET rel_date=?
        WHERE (rel_value=? AND rel_type=1)", array(
        $row['item_datetime'],
        $row['item_id']
    ));
endforeach;

This actually can be simplify using only MySQL query such as:

UPDATE relationship r, item i
    SET r.rel_date=i.item_datetime
    WHERE (r.rel_value=i.item_id AND r.rel_type=1)

Filed under: Code Snippet, MySQL

Tags:

Leave your comment