So, there are 2 tables

1. Table1 (Contains all articles,center,date 00000)
2. Tabla2 (Contains articles handwritten (that are also in Table1),center, date)

We have a procedure that every day compares Table1 and Table2 articles and center, and if they match, an update changes th Table1 date for that article and center.

Now, we also want to add something, we want that in case center is ' ' (empty) on Tabla2, it updates every center that has that article in Table1. Here is the OracleSQL:

               update Table1 r
               set date1= (SELECT max(date2) FROM Tabla2 t 
               where t.articulo = r.articulo
               and t.center = to_char(center) //It gets the center from a select behind
               and t.date2 >= to_char(sysdate,'yyyymmdd')
               group by t.center);

We want both cases to work

1. If center has a real center like 20, it only updates center 20.
2. If center has a empty '' then it updates every center with that article.

CodePudding user response：

I would use something like this:

and t.center = nvl(to_char(center),t.center)

If the center is populated, it will use that value. If center is null, the nvl will instead result in the value of t.center. Basically resulting in t.center=t.center (which again means always true).

CodePudding user response：

You can use:

UPDATE Table1 r
SET date1 = ( SELECT MAX(date2)
                       KEEP (DENSE_RANK FIRST ORDER BY t.center NULLS LAST)
              FROM   Tabla2 t 
              WHERE  t.articulo = r.articulo
              AND    (t.center = r.center OR t.center IS NULL)
              AND    t.date2 >= TRUNC(sysdate)
            );

Note: KEEP (DENSE_RANK LAST... is used to prefer dates from a row with a non-NULL center over rows with a NULL center.

Which, if you have the sample data:

CREATE TABLE table1 (articulo, center, date1) AS
SELECT 1, 1, CAST(NULL AS DATE) FROM DUAL UNION ALL
SELECT 2, 2, NULL FROM DUAL UNION ALL
SELECT 3, 3, NULL FROM DUAL UNION ALL
SELECT 4, 4, NULL FROM DUAL;

CREATE TABLE tabla2 (articulo, center, date2) AS
SELECT 1, 1,    DATE '2023-05-19' FROM DUAL UNION ALL
SELECT 2, 2,    DATE '2023-01-01' FROM DUAL UNION ALL
SELECT 2, 2,    DATE '2023-05-19' FROM DUAL UNION ALL
SELECT 3, 3,    DATE '2023-01-01' FROM DUAL UNION ALL
SELECT 3, NULL, DATE '2023-05-19' FROM DUAL UNION ALL
SELECT 4, NULL, DATE '2023-01-01' FROM DUAL UNION ALL
SELECT 4, NULL, DATE '2023-05-19' FROM DUAL;

Then, after the update Table1 contains:

ARTICULO	CENTER	DATE1
1	1	2023-05-19 00:00:00
2	2	2023-05-19 00:00:00
3	3	2023-01-01 00:00:00
4	4	2023-05-19 00:00:00

db<>fiddle here