ВСТАВЬТЕ и ОБНОВИТЕ значения в таблицу в зависимости от условия
У меня есть 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 имеется одна запись, проверьте в Таблице 2, есть ли присутствует EnrollmentId.
- Если в таблице 2 запись не найдена, никаких действий не требуется.
- Если в таблице 2 найдено несколько записей, обновите соответствующий идентификатор транзакции в таблице 2 для этих записей.
- Если в таблице 1 присутствует более одной записи, проверьте в таблице 2 наличие EnrollmentId.
- Если в таблице 2 запись не найдена, никаких действий не требуется.
- Если в таблице 2 найдена одна запись, обновите/вставьте записи в таблице 2, соответствующие комбинации TransactionId и EnrollmentId вместе со значениями ParameterId, SVCID и SVCValueId. Как EnrollmentId 5 в итоговой таблице результатов.
- Если в таблице 2 найдено несколько записей, каждая запись в таблице 2 должно совпадать с комбинацией таблицы 1. Нравится enrollmentId 7 в финале таблица результатов.
Примечание. Возможная комбинация в таблице 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 — это база данных.
Oracle поддерживает команду MERGE
@MT0 . Я обновил логику, которая охватывает все соображения.
Ответы 1
Вы можете использовать оператор 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.
Идеальный. Это сработало, как и ожидалось. Спасибо.
@MTO есть ли возможность откатить эти обновления? Я просто хочу знать, может ли мне потребоваться откат в случае наихудшего сценария.
@Seegel Если вы не опубликовали COMMIT, то да, как и в случае с любым другим заявлением, вы можете использовать ROLLBACK. Если вы выставили COMMIT, значит, они зафиксированы, и чтобы отменить их, вам нужно будет иметь возможность определить, в чем заключались изменения, и отменить процесс вручную или восстановить их из резервной копии.
Пожалуйста, запишите полный список всей логики того, что должно произойти, когда в
EnrollmentTransactionесть одна или несколько строк, а вEnrollmentDetail— ноль, одно совпадение, одно несовпадение, несколько совпадающих, несколько несовпадающих строк вEnrollmentTransactionна данный момент. сценарии охватывают только половину случаев, и отсутствие полного набора логики означает, что очень сложно угадать, что вы ожидаете.