Home > Software engineering >  How to update a specific value on the table using a temp table with subquery
How to update a specific value on the table using a temp table with subquery

Time：11-15

Hi guys I need some help regarding on the script that I'm doing.

I've created a temp table to get the compared values from table1 and table2

CREATE TABLE tmp_table1 AS
    SELECT a.Date, a.Id, b.Customer_Id, a.Name, a.Values 
    FROM table2 a, table1 b
    WHERE (a.Name like '%Red%' OR a.Name like '%Blue%') 
    AND a.Date = b.Date 
    AND a.Values != b.Values 
    AND a.Customer_Id = b.Customer_Id

Now I would like to update the values from table1 while comparing the values to the temp table using the Customer_Id, Name and Values column. So it should be compared first before adding the column Values to the table1.

UPDATE table1 
SET 
Values = REPLACE(VALUES, '=N', '=N#10-NOV-2022')

Sample Data:

  • Table1
Values = 'ANS1=N, ANS2=Y, ANS3=N'
  • TempTable
Values = 'ANS1=Y, ANS2=N, ANS3=N'

The output should be

  • Table1
Values = 'ANS1=Y, ANS2=N#10-NOV-2022, ANS3=N'

I tried to do it by using this solution but I think I'm missing something.

UPDATE table1
SET Values = REPLACE(Values, '=N', '=N#DATE')
FROM (SELECT b.Values FROM table1 a, tempTable b
        WHERE a.Customer_Id = b.Customer_Id AND a.Values != b.Values)

The date after '=N' is just a hardcoded date.

Do you have any idea regarding on this.

CodePudding user response：

Well, this is a rather cofusing question. Anyway, if I got it right, you already have some data in your Table1 and in your TempTable. One sample row for each could look like this:

Table1

CUST_ID VALS
1 ANS1=N, ANS2=Y, ANS3=N

TempTable

CUST_ID VALS
1 ANS1=Y, ANS2=N, ANS3=N

Now you want to join these two tables by CUST_ID so you could compare the VALS columns (I named the column VALS because VALUES is a reserved keyword in Oracle).
If 'ANS1' part of VALS columns have different values (N and Y) and if 'ANS2' part of VALS columns also have different values (Y and N) then you want to:

  • update the VALS column in Table1 in a way to widen the ANS2 part with # followed by a date.
  • ANS1 and ANS3 part of Table1's VALS column should be like they already are in your TempTable's VALS column.

To do that you could (as one of the options) use functions SubStr(), InStr() and Replace() and CASE expression. In code below your Table1 is named "t1" and your TempTable is named "temp_t1":

Select      SubStr(tt.VALS, 1, Instr(tt.VALS, 'ANS1=')   5)     -- ANS1=Y
        || ', ' ||                                              -- ANS1=Y, 
            CASE 
                WHEN SubStr(SubStr(tt.VALS, Instr(tt.VALS, 'ANS2='), 6), -1) = 'N'   --  If last char of ANS2=N  is  N
                THEN SubStr(                                                         --  THEN concat # concat date
                              Replace (
                                          tt.VALS, SubStr(tt.VALS, Instr(tt.VALS, 'ANS2='), 6), 
                                          SubStr(tt.VALS, Instr(tt.VALS, 'ANS2='), 6) || '#' || To_Char(SYSDATE, 'dd-MON-yyyy')  
                                      ), 
                              Instr(tt.VALS, 'ANS2='), 
                              Instr(tt.VALS, 'ANS3=' ) - Instr(tt.VALS, 'ANS2=')   10 
                          ) END                                 -- ANS1=Y, ANS2=N#11-NOV-2022
        || ', ' ||                                              -- ANS1=Y, ANS2=N#11-NOV-2022
            SubStr(tt.VALS, Instr(tt.VALS, 'ANS3='))"NEW_VALS"  --  result ====>>       ANS1=Y, ANS2=N#11-NOV-2022, ANS3=N   <<==== Left is the final resulting string
From        t1
Inner Join  temp_t1 tt ON(tt.CUST_ID = t1.CUST_ID)
Where       SubStr(t1.VALS, Instr(t1.VALS, 'ANS1=')   5, 1) <> SubStr(tt.VALS, Instr(tt.VALS, 'ANS1=')   5, 1) And
            SubStr(t1.VALS, Instr(t1.VALS, 'ANS2=')   5, 1) <> SubStr(tt.VALS, Instr(tt.VALS, 'ANS2=')   5, 1)

Result is

NEW_VALS
ANS1=Y, ANS2=N#11-NOV-2022, ANS3=N

You can use it with your UPDATE command to do the job:

UPDATE t1
SET t1.VALS =   (
                    Select      SubStr(tt.VALS, 1, Instr(tt.VALS, 'ANS1=')   5)     -- ANS1=Y
                            || ', ' ||                                              -- ANS1=Y, 
                                CASE 
                                    WHEN SubStr(SubStr(tt.VALS, Instr(tt.VALS, 'ANS2='), 6), -1) = 'N'   --  If last char of ANS2=N  is  N
                                    THEN SubStr(                                                         --  THEN concat # concat date
                                                  Replace (
                                                              tt.VALS, SubStr(tt.VALS, Instr(tt.VALS, 'ANS2='), 6), 
                                                              SubStr(tt.VALS, Instr(tt.VALS, 'ANS2='), 6) || '#' || To_Char(SYSDATE, 'dd-MON-yyyy')  
                                                          ), 
                                                  Instr(tt.VALS, 'ANS2='), 
                                                  Instr(tt.VALS, 'ANS3=' ) - Instr(tt.VALS, 'ANS2=')   10 
                                              ) END                                 -- ANS1=Y, ANS2=N#11-NOV-2022
                            || ', ' ||                                              -- ANS1=Y, ANS2=N#11-NOV-2022
                                SubStr(tt.VALS, Instr(tt.VALS, 'ANS3='))"NEW_VALS"  --  result ====>>       ANS1=Y, ANS2=N#11-NOV-2022, ANS3=N   <<==== Left is the final resulting string
                    From        t1
                    Inner Join  temp_t1 tt ON(tt.CUST_ID = t1.CUST_ID)
                    Where       SubStr(t1.VALS, Instr(t1.VALS, 'ANS1=')   5, 1) <> SubStr(tt.VALS, Instr(tt.VALS, 'ANS1=')   5, 1) And
                                SubStr(t1.VALS, Instr(t1.VALS, 'ANS2=')   5, 1) <> SubStr(tt.VALS, Instr(tt.VALS, 'ANS2=')   5, 1)    
                )
WHERE your_where_condition_if_needed

If needed here are all the specific elements of VALS columns from both of your joined tables...

Select      SubStr(tt.VALS, 1, Instr(tt.VALS, 'ANS1=')   5)     -- ANS1=Y
        || ', ' ||                                              -- ANS1=Y, 
            CASE WHEN SubStr(SubStr(tt.VALS, Instr(tt.VALS, 'ANS2='), 6), -1) = 'N'   --  If last char of ANS2=N  is  N
                 THEN SubStr(                                                         --  THEN concat # concat date
                              Replace (   tt.VALS, SubStr(tt.VALS, Instr(tt.VALS, 'ANS2='), 6), 
                                          SubStr(tt.VALS, Instr(tt.VALS, 'ANS2='), 6) || '#' || To_Char(SYSDATE, 'dd-MON-yyyy')  
                                      ), 
                              Instr(tt.VALS, 'ANS2='), 
                              Instr(tt.VALS, 'ANS3=' ) - Instr(tt.VALS, 'ANS2=')   10 
                          ) END                                 -- ANS1=Y, ANS2=N#11-NOV-2022
        || ', ' ||                                              -- ANS1=Y, ANS2=N#11-NOV-2022
            SubStr(tt.VALS, Instr(tt.VALS, 'ANS3='))"NEW_VALS"  --  result ====>>       ANS1=Y, ANS2=N#11-NOV-2022, ANS3=N   <<==== Left is the final resulting string
From        t1
Inner Join  temp_t1 tt ON(tt.CUST_ID = t1.CUST_ID)
Where       SubStr(t1.VALS, Instr(t1.VALS, 'ANS1=')   5, 1) <> SubStr(tt.VALS, Instr(tt.VALS, 'ANS1=')   5, 1) And
            SubStr(t1.VALS, Instr(t1.VALS, 'ANS2=')   5, 1) <> SubStr(tt.VALS, Instr(tt.VALS, 'ANS2=')   5, 1)
T1_ANS1 TEMP_T_ANS1 T1_ANS2 TEMP_T_ANS2 T1_VALS TEMP_T_VALS NEW_ANS_1 NEW_ANS_2 NEW_ANS_3
N Y Y N ANS1=N, ANS2=Y, ANS3=N ANS1=Y, ANS2=N, ANS3=N ANS1=Y ANS2=N#11-NOV-2022 ANS3=N

Regards...

CodePudding user response：

This will update the base table when any of the values in the tmp table changes. If a key/value pair goes from Y to N, it will also append a date string (#DD-MON-YYYY) after that value.

Setup

CREATE table tmp_table1
(date1       DATE
,id          NUMBER
,customer_id NUMBER
,csv_values  VARCHAR2(100)
);

CREATE table table1
(date1       DATE
,id          NUMBER
,customer_id NUMBER
,csv_values  VARCHAR2(100)
);

-- same values
INSERT INTO table1     VALUES(TRUNC(SYSDATE), 1, 10, 'ANS1=Y, ANS2=N#10-OCT-2022, ANS3=Y');
INSERT INTO tmp_table1 VALUES(TRUNC(SYSDATE), 1, 10, 'ANS1=Y, ANS2=N, ANS3=Y');
-- ANS1 Y to N
INSERT INTO table1     VALUES(TRUNC(SYSDATE), 2, 20, 'ANS1=Y, ANS2=Y, ANS3=N#10-OCT-2022');
INSERT INTO tmp_table1 VALUES(TRUNC(SYSDATE), 2, 20, 'ANS1=N, ANS2=Y, ANS3=N');
-- ANS2 Y to N
INSERT INTO table1     VALUES(TRUNC(SYSDATE), 3, 30, 'ANS1=N#10-OCT-2022, ANS2=Y, ANS3=Y');
INSERT INTO tmp_table1 VALUES(TRUNC(SYSDATE), 3, 30, 'ANS1=N, ANS2=N, ANS3=Y');
-- ANS3 Y to N
INSERT INTO table1     VALUES(TRUNC(SYSDATE), 4, 40, 'ANS1=Y,ANS2=N#10-OCT-2022, ANS3=Y');
INSERT INTO tmp_table1 VALUES(TRUNC(SYSDATE), 4, 40, 'ANS1=Y, ANS2=N, ANS3=N');

Used common table expression to split the values column into key/value pairs. Choose to only update existing table when one of the key/value pairs in the tmp table is different. Used a merge statement so it is easy to see what the select from CTE is doing.

MERGE INTO table1 mt
USING
(
WITH key_values 
AS
(
SELECT tt.date1
      ,tt.id
      ,tt.customer_id
      ,tt.csv_values tmp_csv_values
       -- split the csv_values column in temp table into pieces
      ,REGEXP_SUBSTR(tt.csv_values, '\w =\w ', 1, 1) tmp_kv_1
      ,REGEXP_SUBSTR(tt.csv_values, '\w =\w ', 1, 2) tmp_kv_2
      ,REGEXP_SUBSTR(tt.csv_values, '\w =\w ', 1, 3) tmp_kv_3
      ,t_1.csv_values existing_csv_values
       -- split the csv_values column in table1 into pieces on key=value
      ,REGEXP_SUBSTR(t_1.csv_values, '\w =\w ', 1, 1) existing_kv_1
       -- split the csv_values column in table1 into pieces on key=value and include date if there
      ,REGEXP_SUBSTR(t_1.csv_values, '\w =\w (#\d\d-[A-Z]{3,3}-\d\d\d\d)?', 1, 1) existing_kv1_plus_dt
      ,REGEXP_SUBSTR(t_1.csv_values, '\w =\w ', 1, 2) existing_kv_2
      ,REGEXP_SUBSTR(t_1.csv_values, '\w =\w (#\d\d-[A-Z]{3,3}-\d\d\d\d)?', 1, 2) existing_kv2_plus_dt
      ,REGEXP_SUBSTR(t_1.csv_values, '\w =\w ', 1, 3) existing_kv_3
      ,REGEXP_SUBSTR(t_1.csv_values, '\w =\w (#\d\d-[A-Z]{3,3}-\d\d\d\d)?', 1, 3) existing_kv3_plus_dt
      ,'#' || TO_CHAR(SYSDATE, 'DD-MON-RRRR') append_date
FROM   tmp_table1 tt
       JOIN table1 t_1 ON (tt.date1 = t_1.date1 AND tt.customer_id = t_1.customer_id)
)
SELECT kv.date1
      ,kv.id
      ,kv.customer_id
      ,kv.existing_csv_values
      ,kv.tmp_csv_values
      ,CASE
         WHEN kv.tmp_kv_1 != kv.existing_kv_1
           -- when not equal use new values. append date if changed from Y to N
           THEN kv.tmp_kv_1 || DECODE(INSTR(kv.tmp_kv_1, '=Y'), 0, kv.append_date)
         ELSE -- when equal put back what was there
           kv.existing_kv1_plus_dt
       END
       ||', '||
       CASE
         WHEN kv.tmp_kv_2 != kv.existing_kv_2
           THEN kv.tmp_kv_2 || DECODE(INSTR(kv.tmp_kv_2, '=Y'), 0, kv.append_date)
         ELSE
           kv.existing_kv2_plus_dt
       END
       ||', ' ||
       CASE
         WHEN kv.tmp_kv_3 != kv.existing_kv_3
           THEN kv.tmp_kv_3 || DECODE(INSTR(kv.tmp_kv_3, '=Y'), 0, kv.append_date)
         ELSE
           kv.existing_kv3_plus_dt
       END new_csv_values
FROM   key_values kv
WHERE  (kv.tmp_kv_1 != kv.existing_kv_1
        OR kv.tmp_kv_2 != kv.existing_kv_2
        OR kv.tmp_kv_3 != kv.existing_kv_3)
) ij
ON (mt.customer_id = ij.customer_id)
WHEN MATCHED THEN UPDATE SET mt.csv_values = ij.new_csv_values

Results of select from CTE

DATE1 ID CUSTOMER_ID EXISTING_CSV_VALUES TMP_CSV_VALUES NEW_CSV_VALUES
2022-11-14 00:00:00 2 20 ANS1=Y, ANS2=Y, ANS3=N#10-OCT-2022 ANS1=N, ANS2=Y, ANS3=N ANS1=N#14-NOV-2022, ANS2=Y, ANS3=N#10-OCT-2022
2022-11-14 00:00:00 3 30 ANS1=N#10-OCT-2022, ANS2=Y, ANS3=Y ANS1=N, ANS2=N, ANS3=Y ANS1=N#10-OCT-2022, ANS2=N#14-NOV-2022, ANS3=Y
2022-11-14 00:00:00 4 40 ANS1=Y,ANS2=N#10-OCT-2022, ANS3=Y ANS1=Y, ANS2=N, ANS3=N ANS1=Y, ANS2=N#10-OCT-2022, ANS3=N#14-NOV-2022

Page link：https//www.codepudding.com/Softwareengineering/610313.html

Prev:Analytic functions and means of window clause
Next:Terraform AWS: Cannot output VPC endpoints. Unsupported attribute
  • Related

About Us:  Contact Us      Terms of Service       Privacy Policy

Copyright © 2010-2023，Powered By CodePudding