/*
CREATE TEMP TABLE organisations (id INT, isarchive BOOLEAN) WITH NO LOG;
CREATE TEMP TABLE payments (receiverid INT, date_ DATE) WITH NO LOG;
*/

UPDATE organisations
   SET isarchive = 'f'
 WHERE id IN (SELECT id
               FROM (SELECT receiverid AS id
                           ,MAX(date_) AS max
                      FROM payments
                     GROUP BY receiverid
                    HAVING MAX(date_) <= TO_DATE("2016-01-01", "%Y-%m-%d"))
 );

DROP TABLE IF EXISTS tmptb;

 SELECT id
  FROM (SELECT receiverid AS id
              ,MAX(date_) AS max
         FROM payments
        GROUP BY receiverid
       HAVING MAX(date_) <= TO_DATE("2016-01-01", "%Y-%m-%d"))
INTO TEMP tmptb WITH NO LOG;

CREATE UNIQUE INDEX i_tmptb ON tmptb (id);

DROP PROCEDURE IF EXISTS tmpproc;
CREATE PROCEDURE tmpproc()
 DEFINE p_id INT;
 FOREACH WITH HOLD
  SELECT id
    INTO p_id
    FROM tmptb
  UPDATE organisations
     SET isarchive = 'f'
   WHERE id == p_id;
 END FOREACH;
END PROCEDURE;

EXECUTE PROCEDURE tmpproc();
DROP PROCEDURE tmpproc;
DROP TABLE tmptb;