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;

Queries to delete duplicates in a table

Three useful queries that I use to delete duplicate rows from tables

DELETE FROM customers
 WHERE id IN (SELECT id
                FROM (SELECT id,
                             lastname,
                             firstname,
                             RANK ()
                             OVER (PARTITION BY lastname, firstname
                                   ORDER BY id)
                                 AS seqnumber
                        FROM customers)
               WHERE seqnumber > 1);

DELETE FROM table_name
 WHERE ROWID NOT IN 
 (SELECT MAX (ROWID) 
    FROM TABLE 
    GROUP BY duplicate_values_field_name);

SELECT *
  FROM moon.sub_globals
 WHERE ROWID IN (SELECT ROWID
                   FROM (SELECT ROWID,
                                subscriber_id,
                                sub_member_id,
                                RANK ()
                                OVER (
                                    PARTITION BY subscriber_id, sub_member_id
                                    ORDER BY ROWID)
                                    AS seqnumber
                           FROM moon.sub_globals) a
                  WHERE seqnumber > 1)