CODEWITHSUNDEEP
sqloracle-databasesql-update
Saafi
Saafi

Reputation: 25

Update Query For ORACLE SQL DEVELOPER

I have four columns

id fisrt_name last_name city
101 A B C
303 A B C
207 A B C
55 X Y Z
67 X Y Z
200 X Y Z

Basically FN, LN and city are same but the ids are different for these same values. I want to UPDATE the Ids of the duplicate rows as same, shown in table below using oracle sql developer. The id can be update to either of the three but it should become same for all the three duplicate rows.

id fisrt_name last_name city
101 A B C
101 A B C
101 A B C
55 X Y Z
55 X Y Z
55 X Y Z

I tried this

UPDATE TABLE T1 SET
T1.ID = ID
WHERE ROWID IN (SELECT ROWID FROM(
SELECT ROWID,ROW_NUMBER() OVER(PARTITION BY ID, CITY ORDER BY ADR_LINE_1) AS RN, ID, CITY 
FROM TABLE
WHERE (first_name,last_name,city) IN (first_name,last_name,city
from table 
group by first_name,last_name,city
having count(distinct id) >= 2)) WHERE RN = 1);

It updates the desired rows but with the same ids not with what I want.

Upvotes: 0

Views: 2128

Answers (3)

GMB
GMB

Reputation: 222482

I would go for a correlated subquery:

update t1 
set id = (
    select min(id) 
    from mytable t1 
    where t1.first_name = t.first_name and t1.lastname = t.lastname and t1.city = t.city
)
where id > (
    select min(id) 
    from mytable t1 
    where t1.first_name = t.first_name and t1.lastname = t.lastname and t1.city = t.city
)

This query would take advantage of an index on (firstname, lastname, city, id) - although updating the rows will require updating the index too...

Upvotes: 0

Barbaros Özhan
Barbaros Özhan

Reputation: 65313

One option would be using MIN() Analytic Function with grouping by repeating three columns (first_name, last_name, city) through a MERGE Statement such as

MERGE INTO tab t1
USING ( SELECT MIN(id) OVER (PARTITION BY first_name, last_name, city) AS new_id
          FROM tab t ) t2
   ON ( t1.rowid = t2.rowid )                 
 WHEN MATCHED THEN UPDATE SET t1.id = t2.new_id

Demo

Upvotes: 0

user5683823
user5683823

Reputation:

Here's one way - join your table to the result of an aggregate query and update (group by fn, ln, city, filter out the groups with a single id, and select min(id) in the remaining groups, then use that to update).

Set up the test case:

create table my_table (id, first_name, last_name, city) as
  select 101, 'A', 'B', 'C' from dual union all
  select 303, 'A', 'B', 'C' from dual union all
  select 207, 'A', 'B', 'C' from dual union all
  select  55, 'X', 'Y', 'Z' from dual union all
  select  67, 'X', 'Y', 'Z' from dual union all
  select 200, 'X', 'Y', 'Z' from dual union all
  select 333, 'D', 'F', 'G' from dual
;

Table MY_TABLE created.

Update:

update
( select t.id, g.min_id
  from   my_table t
         inner join
         ( select min(id) as min_id, first_name, last_name, city
           from   my_table
           group  by first_name, last_name, city
           having min(id) != max(id)
         ) g
         using (first_name, last_name, city)
)
set id = min_id
where id != min_id
;

4 rows updated.

Check the result:

select * from my_table;

        ID FIRST_NAME LAST_NAME  CITY      
---------- ---------- ---------- ----------
       101 A          B          C         
       101 A          B          C         
       101 A          B          C         
        55 X          Y          Z         
        55 X          Y          Z         
        55 X          Y          Z         
       333 D          F          G

NOTE: If ID may be null, that will require a bit of additional handling (but, the column should be NOT NULL.... is it?)

Upvotes: 1

Related Questions