Search code examples
sqloracle-databaseoracle11g

how to update same column using update query

enter image description here

I am trying to update flag column based on cif. I cant figure it out. Kindly help to make update/merge query

enter image description here

Solution

  • You can use a correlated UPDATE:

    UPDATE table_name t
SET   flag = (SELECT x.flag
              FROM   table_name x
              WHERE  t.cif = x.cif
              AND    x.flag IS NOT NULL
              AND    ROWNUM = 1)
WHERE flag IS NULL;

    Slightly more complicated but (probably) more efficient would be to use MERGE and correlate on the ROWID pseudo-column (which is a pointer to the row):

    MERGE INTO table_name dst
USING (
  SELECT max_flag
  FROM   (
    SELECT flag,
           MAX(flag) OVER (PARTITION BY cif) AS max_flag
    FROM   table_name
  )
  WHERE  flag IS NULL
  AND    max_flag IS NOT NULL
) src
ON (dst.ROWID = src.ROWID)
WHEN MATCHED THEN
  UPDATE
  SET   flag = src.max_flag;

    Which, for the sample data:

    CREATE TABLE table_name (cif, flag) AS
SELECT 120, 'SF' FROM DUAL UNION ALL
SELECT 120, 'NA' FROM DUAL UNION ALL
SELECT 151, 'NA' FROM DUAL UNION ALL
SELECT 156, 'MF' FROM DUAL UNION ALL
SELECT 156, 'MF' FROM DUAL UNION ALL
SELECT 165, NULL FROM DUAL UNION ALL
SELECT 165, 'SF' FROM DUAL UNION ALL
SELECT 165, 'SF' FROM DUAL UNION ALL
SELECT 158, 'MF' FROM DUAL UNION ALL
SELECT 158, NULL FROM DUAL;

    Then, after the UPDATE or MERGE, the table contains:

    CIF FLAG
    120 SF
    120 NA
    151 NA
    156 MF
    156 MF
    165 SF
    165 SF
    165 SF
    158 MF
    158 MF

    fiddle