Sunday, August 7, 2011

Updating a table based on rows from another table

Example One:
UPDATE a
   SET data =
           (SELECT data
              FROM b
             WHERE id1 = a.id1 AND id2 = a.id2)
 WHERE EXISTS
           (SELECT 1
              FROM b
             WHERE id1 = a.id1 AND id2 = a.id2);
Example two, can be used for multiple columns
UPDATE (SELECT a.data a_data, b.data b_data
          FROM a, b
         WHERE a.id1 = b.id1 AND a.id2 = b.id2)
   SET a_data = b_data;

No comments:

Post a Comment