У меня есть 2 таблицы:
Таблица 1: EnrollmentTransaction (ПК — оба столбца)
EnrollmentId | TransactionId
-------------+--------------
5 | 1
5 | 2
6 | 3
7 | 2
7 | 3
8 | 3
8 | 2
8 | 1
9 | 1
Таблица 2: EnrollmentDetail (столбец TransactionId добавлен заново со значением по умолчанию 1, поскольку это не нулевой столбец)
В этой таблице ПК указаны (EnrollmentId, ПараметрId, SVCId и TransactionId).
EnrollmentId | ParameterId| SVCId| SVCValueId| TransactionId
-------------+------------+------+-----------+--------------
5 | 1 | 57 | 21 | 1
6 | 8 | 58 | 24 | 1
7 | 9 | 57 | 21 | 1
7 | 6 | 58 | 29 | 1
8 | 8 | 57 | 21 | 1
Ссылаясь на таблицу 1, я хотел бы вставить, если нет комбинации (EnrollmentId, TransactionId) или обновить, если есть запись на основе EnrollmentId.
Например:
Сценарий 1: EnrollmentId = 5 имеет 2 записи в таблице 1, тогда как в таблице 2 есть только 1 запись, и я должен вставить одну строку со значениями 5, 1, 57, 21, 2 в таблицу 2. Мне не следует вносить какие-либо изменения в существующую таблицу 1. комбинированный ряд (5, 1, 57, 21, 1). поскольку он соответствует комбинации Enrollment Id и TrnasactionId.
Сценарий 2: EnrollmentId = 6 имеет только одну запись в таблице 1 и таблице 2, но TransactionId в обеих таблицах разный. Поэтому теперь мне нужно сослаться на таблицу 1 для TransactionId и обновить таблицу 2. В основном TrasnactionId будет окончательным значением для обновления из таблицы 1. Окончательными значениями этой строки будут 6, 8, 58, 24 и 3.
Сценарий 3: EnrollmentId = 9 имеет только 1 запись в таблице 1, а в таблице 2 запись не найдена. Поэтому мне не следует вставлять/обновлять эту комбинацию.
Логические соображения.
Примечание. Возможная комбинация в таблице 1 для каждого enrollmentId будет состоять из трех значений. то есть 1, 2 или 3.
Это разовая деятельность, и она продолжается до начала производства.
Окончательный результат будет таким, как показано ниже.
EnrollmentId | ParameterId| SVCId| SVCValueId| TransactionId
-------------+------------+---------------------------------
5 | 1 | 57 | 21 | 1
5 | 1 | 57 | 21 | 2
6 | 8 | 58 | 24 | 3
7 | 9 | 57 | 21 | 2
7 | 6 | 58 | 29 | 2
7 | 9 | 57 | 21 | 3
7 | 6 | 58 | 29 | 3
8 | 8 | 57 | 21 | 1
8 | 8 | 57 | 21 | 2
8 | 8 | 57 | 21 | 3
Есть ли возможность написать один запрос для достижения этой цели? Мне нужно добиться как вставки, так и обновления в одном и том же запросе.
Примечание. Oracle 19 — это база данных.
🤔 А знаете ли вы, что...
SQL поддерживает создание представлений (VIEW), что позволяет абстрагироваться от структуры таблицы при выполнении запросов.
Вы можете использовать оператор MERGE и объединять строки на основе определения приоритета совпадающих значений transactionid:
MERGE INTO enrollmentdetail dst
USING (
SELECT t.enrollmentid,
t.transactionid,
d.parameterid,
d.svcid,
d.svcvalueid,
d.rowid AS rid,
ROW_NUMBER() OVER (
PARTITION BY d.ROWID
ORDER BY CASE WHEN d.transactionid = t.transactionid THEN 0 ELSE 1 END
) AS rn
FROM enrollmenttransaction t
INNER JOIN enrollmentdetail d
ON t.enrollmentid = d.enrollmentid
) src
ON (dst.ROWID = src.rid AND src.rn = 1)
WHEN MATCHED THEN
UPDATE
SET parameterid = src.parameterid,
transactionid = src.transactionid,
svcid = src.svcid,
svcvalueid = src.svcvalueid
WHEN NOT MATCHED THEN
INSERT (enrollmentid, transactionid, parameterid, svcid, svcvalueid)
VALUES (src.enrollmentid, src.transactionid, src.parameterid, src.svcid, src.svcvalueid);
Что для примера данных:
CREATE TABLE EnrollmentTransaction (EnrollmentId, TransactionId) AS
SELECT 5, 1 FROM DUAL UNION ALL
SELECT 5, 2 FROM DUAL UNION ALL
SELECT 6, 3 FROM DUAL UNION ALL
SELECT 7, 2 FROM DUAL UNION ALL
SELECT 7, 3 FROM DUAL UNION ALL
SELECT 8, 3 FROM DUAL UNION ALL
SELECT 8, 2 FROM DUAL UNION ALL
SELECT 8, 1 FROM DUAL UNION ALL
SELECT 9, 1 FROM DUAL;
CREATE TABLE EnrollmentDetail (
EnrollmentId, ParameterId, TransactionId, SVCId, SVCValueID
) AS
SELECT 5, 1, 1, 57, 21 FROM DUAL UNION ALL
SELECT 6, 8, 1, 58, 24 FROM DUAL UNION ALL
SELECT 7, 9, 1, 57, 21 FROM DUAL UNION ALL
SELECT 7, 6, 1, 58, 29 FROM DUAL UNION ALL
SELECT 8, 8, 1, 57, 21 FROM DUAL;
Тогда после MERGE таблица содержит:
Примечание. Это дает ожидаемый ответ для вашего образца данных; пожалуйста, проверьте логику для других сценариев, которых нет в вашем образце данных, поскольку ваша логика сложна и ее нелегко выразить с помощью SQL.