CODEWITHSUNDEEP
sqloracle-database
Al Sol
Al Sol

Reputation: 19

SQL Apply Value to subsequent rows

PK Field2 Field3
11111 a1 0 00:05
11111 a2 0 00:05
22222 a1 0 00:05

I want to update ALL values in Field3 for '11111' to '0 00:10' only if a2 in Field2 is present. How can I achieve that?

Upvotes: 0

Views: 31

Answers (1)

MT0
MT0

Reputation: 168281

You can use an UPDATE with an EXISTS filter condition:

UPDATE table_name t
SET field3 = '0 00:10'
WHERE pk = 11111
AND EXISTS(
  SELECT 1
  FROM   table_name x
  WHERE  t.pk     = x.pk
  AND    x.field2 = 'a2'
)

or a MERGE statement with a conditional analytic function:

MERGE INTO table_name dst
USING (
  SELECT COUNT(CASE field2 WHEN 'a2' THEN 1 END) OVER () AS cnt
  FROM   table_name
  WHERE  pk = 11111
) src
ON (dst.ROWID = src.ROWID AND src.cnt = 1)
WHEN MATCHED THEN
  UPDATE SET Field3 = '0 00:10';

Which, for the sample data:

CREATE TABLE table_name (PK, Field2, Field3) AS
SELECT 11111, 'a1', '0 00:05' FROM DUAL UNION ALL
SELECT 11111, 'a2', '0 00:05' FROM DUAL UNION ALL
SELECT 22222, 'a1', '0 00:05' FROM DUAL;

Then, after the UPDATE or MERGE:

SELECT * FROM table_name;

Outputs:

PK FIELD2 FIELD3
11111 a1 0 00:10
11111 a2 0 00:10
22222 a1 0 00:05

db<>fiddle here

If you just want to use a SELECT statement then:

SELECT pk,
       field2,
       CASE
       WHEN pk = 11111
       AND  COUNT(CASE field2 WHEN 'a2' THEN 1 END) OVER (PARTITION BY pk) > 0
       THEN '0 00:10'
       ELSE field3
       END AS field3
FROM   table_name;

or

SELECT pk,
       field2,
       CASE
       WHEN pk = 11111
       AND  EXISTS(
         SELECT 1
         FROM   table_name x
         WHERE  x.pk = t.pk
         AND    x.field2 = 'a2'
       )
       THEN '0 00:10'
       ELSE field3
       END AS field3
FROM   table_name t;

db<>fiddle here

Upvotes: 1

Related Questions