RDTEX Oracle Blog

CREATE TABLE test_clients
(
   id         NUMBER,
   name       VARCHAR2(30),
   phone      VARCHAR2(30),
   phone_type VARCHAR2(20)
);
 
INSERT INTO test_clients
VALUES (1, 'Вася', NULL, NULL);
 
INSERT INTO test_clients
VALUES (2, 'Петя', NULL, NULL);
 
CREATE TABLE test_phones
(
   client_id   NUMBER,
   phone       VARCHAR2 (30),
   phone_type  VARCHAR2 (20)
);
 
INSERT INTO test_phones
VALUES (1, '8(905)133-30-10', 'Мобильный');
 
INSERT INTO test_phones
VALUES (2, '8(905)247-89-17', 'Мобильный');

MERGE INTO test_clients dest
  USING 
    (WITH f AS 
      (SELECT c.ROWID AS rid,
              NVL(p.client_id, c.id) AS id,
              p.phone,
              p.phone_type,
              CASE
                WHEN c.id IS NULL THEN 'I'
                WHEN p.client_id IS NULL THEN 'D'
              ELSE
                CASE ROW_NUMBER() 
                     OVER (PARTITION BY p.client_id 
                     ORDER BY p.phone)
                WHEN 1 THEN 'U' 
                ELSE 'I'
                END
              END AS r,
              ROW_NUMBER() 
              OVER (PARTITION BY NVL(c.id, p.client_id)
                    ORDER BY 1) AS rn
         FROM test_phones p
              FULL OUTER JOIN
              test_clients c
              ON c.id = p.client_id AND NVL(c.phone, p.phone) = p.phone
        WHERE (p.client_id IS NOT NULL AND NVL(c.phone_type, '-') != p.phone_type)
           OR (p.client_id IS NULL))
     SELECT f.*, s.name
       FROM f JOIN (SELECT DISTINCT id, name FROM test_clients) s ON s.ID = f.ID) src
    ON (dest.ROWID = src.rid AND src.r IN ('U', 'D'))
WHEN MATCHED THEN
   UPDATE SET phone = src.phone,
              phone_type = src.phone_type
   DELETE WHERE src.r = 'D' AND src.rn > 1
WHEN NOT MATCHED THEN
   INSERT VALUES (src.id,
                  src.name,
                  src.phone,
                  src.phone_type);

INSERT INTO test_phones 
VALUES (2, '8(496)245-55-86', 'Второй домашний');
 
DELETE FROM test_phones
WHERE client_id = 2 AND ROWNUM <= 1;