es.davy.ai

Preguntas y respuestas de programación confiables

¿Tienes una pregunta?

Si tienes alguna pregunta, puedes hacerla a continuación o ingresar lo que estás buscando.

Cómo utilizar la instrucción “merge” en SQL para cumplir con ciertas condiciones?

13 Jul, 2023 Programación

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í:

Descripción de la imagen

La RONDA 2 se ve así:
Descripción de la imagen

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...
Tags:  , ,

Answer

  1. Avatar for davy.ai
    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:

    MERGE INTO TBL_B B
USING (
    SELECT    
        PERSON_IDENTIFICATION,
        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),'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
    and A.LAST_STATUS > B.LAST_STATUS -- condición añadida
)
WHEN MATCHED THEN 
    UPDATE SET 
        B.LAST_STATUS = A.LAST_STATUS,
        B.BOUGHT_DATETIME = GREATEST(B.BOUGHT_DATETIME, A.BOUGHT_DATETIME),
        B.CLAIMED_DATETIME = GREATEST(B.CLAIMED_DATETIME, A.CLAIMED_DATETIME),
        B.RETURNED_DATETIME = GREATEST(B.RETURNED_DATETIME, A.RETURNED_DATETIME),
        B.EXPIRATION_DATETIME = A.EXPIRATION_DATETIME
WHEN NOT MATCHED THEN 
    INSERT (
        PERSON_IDENTIFICATION, 
        TRANSACTION_IDENTIFICATION, 
        LAST_STATUS, 
        BOUGHT_DATETIME, 
        CLAIMED_DATETIME, 
        RETURNED_DATETIME,
        EXPIRATION_DATETIME
    ) VALUES (
        A.PERSON_IDENTIFICATION, 
        A.TRANSACTION_IDENTIFICATION, 
        A.LAST_STATUS, 
        A.BOUGHT_DATETIME, 
        A.CLAIMED_DATETIME, 
        A.RETURNED_DATETIME,
        A.EXPIRATION_DATETIME
    );

    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.

Comments are closed.