Welcome, Guest: Register On Nairaland / LOGIN! / Trending / Recent / New
Stats: 3,143,272 members, 7,780,612 topics. Date: Thursday, 28 March 2024 at 05:36 PM

Help Out With This Oracle Query - Programming - Nairaland

Nairaland Forum / Science/Technology / Programming / Help Out With This Oracle Query (1379 Views)

Media Query In Css Is Not Working On All My Laptop Browsers / His Mysql Query Returned Duplicate Output. Help Him / How Do I Search Multiple Tables In A Database For A Query? (2) (3) (4)

(1) (Reply)

Help Out With This Oracle Query by ToyinDipo(m): 11:06am On Mar 19, 2015
1. update table_name set sig_sn = 0 where cust_id in
(select cust_id from table_name group by cust_id having count(*)>1)

2. update table_name a set a.sig_sn = ((select max(b.sig_sn) from table_name b
where b.sig_sn = a.sig_sn) + 1)
where a.cust_id in (select cust_id from table_name group by cust_id having count(*)>1)


Can someone help me out with the query above in oracle 11g.
I want to create a unique index on the combo of cust_id and sig_sn fields.
cust_id column may contain duplicate records, but cust_id and sig_sn must be unique for each row.
But before then, I have to put the data in proper form, so as not to violate unique constraint.
I have google searched with no solution found so far.
I'm trying to update the table using self join.


The idea is to first set all sig_sn field to 0 for all records that have duplicated cust_id
That I achieved with query 1.

However query 2, which is the backbone is failing.

I want to update by navigating through the records with duplicate cust_id, such that
it selects the max(sig_sn) for a cust_id each time, and adds 1 to it, and updates the
current record sig_sn field with the result.
The table b, holds the copy, that I want to fetch the current max sig_sn value from, for each record update.
But it seems the records in the table b, are not updated for each parse.
So it fetches 0 as max(sig_sn) each time.
Re: Help Out With This Oracle Query by Ladylite: 12:43am On Jan 23, 2020
Who you? Why are you contacting me

(1) (Reply)

Android AIDE Premium Key Problem / Requirements For Andela's Bootcamp / Beginners Android Studio HOW TOS.Learn It Here

(Go Up)

Sections: politics (1) business autos (1) jobs (1) career education (1) romance computers phones travel sports fashion health
religion celebs tv-movies music-radio literature webmasters programming techmarket

Links: (1) (2) (3) (4) (5) (6) (7) (8) (9) (10)

Nairaland - Copyright © 2005 - 2024 Oluwaseun Osewa. All rights reserved. See How To Advertise. 9
Disclaimer: Every Nairaland member is solely responsible for anything that he/she posts or uploads on Nairaland.