Cómo utilizar la instrucción “merge” en SQL para cumplir con ciertas condiciones?
Tengo TBL_B que contiene estas columnas:
DROP TABLE TBL_B;
CREATE TABLE TBL_B (
PERSON_IDENFITICATION VARCHAR(100) NOT NULL,
TRANSACTION_IDENTIFICATION VARCHAR(100) NOT NULL,
LAST_STATUS VARCHAR(100) NOT NULL,
BOUGHT_DATETIME DATETIME NOT NULL,
CLAIMED_DATETIME DATETIME NOT NULL,
RETURNED_DATETIME DATETIME NOT NULL,
EXPIRATION_DATETIME DATETIME NOT NULL
);
Tengo TBL_1 que en la RONDA 1 se ve así:
El ESTADO siempre sigue este orden: BOUGHT > CLAIMED > RETURNED. Es posible que no tengas todos los estados para cada transacción, pero siempre seguirá este orden.
En la función de unión, me gustaría insertar un registro si se encuentra un estado proveniente de TBL_1 en la RONDA 2 (siguiente tabla) que sea MAYOR a lo que se había actualizado previamente. Por ejemplo, la transacción “222” no tenía el estado “RETURNED” previamente, ahora se ha agregado. Quiero que se inserte este registro. Para la transacción “111”, la RONDA 1 tenía el estado “RETURNED”, ahora no lo tiene. No quiero que se actualice este registro.
Tengo esta consulta hasta ahora, pero tengo problemas para ingresar las condiciones exactas para actualizar solo si el último estado en Ronda 2 es mayor y ha sido actualizado.
MERGE INTO TBL_B B
USING (
SELECT
PERSON_IDENFITICATION,
TRANSACTION_IDENTIFICATION
,(array_agg(STATUS) within group(order by STATUS_DATETIME desc)[0])::varchar as LAST_STATUS
,coalesce(max(case when STATUS = 'BOUGHT' THEN STATUS_DATETIME END), max(case when STATUS = 'CLAIMED' THEN STATUS_DATETIME END), max(case when STATUS = 'RETURNED' THEN STATUS_DATETIME END), '1900-01-01'::datetime) as BOUGHT_DATETIME
,coalesce(max(case when STATUS = 'CLAIMED' THEN STATUS_DATETIME END),'1900-01-01'::datetime) as CLAIMED_DATETIME
,coalesce(max(case when STATUS = 'RETURNED' THEN STATUS_DATETIME END),'1900-01-01'::datetime) as RETURNED_DATETIME
,EXPIRATION_DATETIME
FROM TBL_1
GROUP BY 1,2,7
) A ON (A.PERSON_IDENTIFICATION = B.PERSON_IDENTIFICATION
and A.TRANSACTION_IDENTIFICATION = B.TRANSACTION_IDENTIFICATION)
WHEN MATCHED THEN
UPDATE SET...
davy.ai
Para actualizar solo si el último estado en la RONDA 2 es mayor, puedes agregar una condición en la cláusula ON que verifique si el LAST_STATUS en TBL_1 (de la RONDA 2) es mayor que el LAST_STATUS en TBL_B (de la RONDA 1).
Aquí tienes la consulta modificada con la condición añadida:
En la cláusula ON, la condición añadida
A.LAST_STATUS > B.LAST_STATUS
verifica si el LAST_STATUS en TBL_1 (de la RONDA 2) es mayor que el LAST_STATUS en TBL_B (de la RONDA 1).En la cláusula UPDATE, se utiliza la función
GREATEST
para actualizar las columnas de DATETIME. Esta función devuelve el valor más grande entre los valores especificados. Así,GREATEST(B.BOUGHT_DATETIME, A.BOUGHT_DATETIME)
devuelve la fecha más reciente entre la BOUGHT_DATETIME existente en TBL_B y la nueva BOUGHT_DATETIME en TBL_1. Lo mismo se aplica a las demás columnas de DATETIME.En la cláusula INSERT, los valores se insertan en TBL_B si no hay coincidencia.