CODEWITHSUNDEEP
sqlsnowflake-cloud-data-platform
user3461502
user3461502

Reputation: 323

Updating several columns in one table from another table with a single column?

TABLE A that looks like this: enter image description here

TABLE B that looks like this: enter image description here

Using TABLE A I need to update TABLE B in a SINGLE row, and use the DATETIME column in TABLE A to get the values in each of the columns in TABLE B (ignoring the status that is shown in TABLE B single record). So from the three records in TABLE A, I need to have only one record and put the date that matches with the corresponding STATUS, and if the status is not found, put the dates in Table B to: '2999-12-31 23:59:5'

 when status = 'CLAIMED' put DATETIME in CLAIMED_DATETIME
 when status = 'BOUGHT' put DATETIME in BOUGHT_DATETIME
 when status = 'RETURNED' put DATETIME in RETURNED_DATETIME

I tried this query but it only updates the first column, and it's not updating the rest.

UPDATE TABLE B a
SET
    CLAIMED_datetime = IFF(STATUS='claimed', DATETIME, '2999-12-31 23:59:5'),
    BOUGHT_datetime = IFF(STATUS='bought', DATETIME, '2999-12-31 23:59:5'),
    RETURNED_datetime = IFF(STATUS='returned', DATETIME, '2999-12-31 23:59:5'),
FROM TABLE A SRC
WHERE a.number_id = SRC.number_id
and a.country= SRC.country

I need table B to look like this: enter image description here

Does anyone know what is wrong the query or how to do this?

Upvotes: 1

Views: 76

Answers (1)

Eric Lin
Eric Lin

Reputation: 1510

The reason that only first one got updated is because once we matched the row, it won't get updated again, even though the JOIN caused 3 rows to be returned. The first row had the status of CLAIMED, and it got updated, then the rest of two matched rows will be ignored.

As Patrick mentioned, we can try to use PIVOT function reduce 3 rows from table A to one row with status on its own columns.

Below query assumes that there will only be one DATETIME value per number_id and country:

create or replace table a (
    number_id int, country varchar, status varchar, datetime date
);
insert into a values 
(121144, 'USA', 'CLAIMED', '2021-10-10'),
(121144, 'USA', 'BOUGHT', '2021-10-11'),
(121144, 'USA', 'RETURNED', '2021-10-12'),
(121144, 'AU', 'CLAIMED', '2021-09-10'),
(121144, 'AU', 'BOUGHT', '2021-09-11');

create or replace table b (
    number_id int, country varchar, status varchar, 
    claimed_date date, bought_date date, returned_date date
);
insert into b values 
(121144, 'USA', 'RETURNED', '2999-12-31', '2999-12-31', '2999-12-31'),
(121144, 'AU', 'BOUGHT', '2999-12-31', '2999-12-31', '2999-12-31');



MERGE INTO B
USING (
    SELECT * FROM A 
    PIVOT(MIN(DATETIME) FOR STATUS IN ('CLAIMED', 'BOUGHT', 'RETURNED')) 
     AS P (NUMBER_ID, COUNTRY, CLAIMED, BOUGHT, RETURNED)
) A ON (A.NUMBER_ID = B.NUMBER_ID and A.COUNTRY = B.COUNTRY)
WHEN MATCHED THEN
  UPDATE SET
    B.CLAIMED_DATE = IFF(A.CLAIMED IS NOT NULL, A.CLAIMED, '2999-12-31 23:59:5'),
    B.BOUGHT_DATE = IFF(A.BOUGHT IS NOT NULL, A.BOUGHT, '2999-12-31 23:59:5'),
    B.RETURNED_DATE = IFF(A.RETURNED IS NOT NULL, A.RETURNED, '2999-12-31 23:59:5');

Before MERGE:

+-----------+---------+----------+--------------+-------------+---------------+
| NUMBER_ID | COUNTRY | STATUS   | CLAIMED_DATE | BOUGHT_DATE | RETURNED_DATE |
|-----------+---------+----------+--------------+-------------+---------------|
|    121144 | USA     | RETURNED | 2999-12-31   | 2999-12-31  | 2999-12-31    |
|    121144 | AU      | BOUGHT   | 2999-12-31   | 2999-12-31  | 2999-12-31    |
+-----------+---------+----------+--------------+-------------+---------------+

After MERGE:

+-----------+---------+----------+--------------+-------------+---------------+
| NUMBER_ID | COUNTRY | STATUS   | CLAIMED_DATE | BOUGHT_DATE | RETURNED_DATE |
|-----------+---------+----------+--------------+-------------+---------------|
|    121144 | USA     | RETURNED | 2021-10-10   | 2021-10-11  | 2021-10-12    |
|    121144 | AU      | BOUGHT   | 2021-09-10   | 2021-09-11  | 2999-12-31    |
+-----------+---------+----------+--------------+-------------+---------------+

If the statuses are fixed, i.e., only with values of CLAIMED, BOUGHT and RETURNED, maybe you can redesign your table to put them into columns instead?

Upvotes: 1

Related Questions