Welcome, Guest: Register On Nairaland / LOGIN! / Trending / Recent / NewStats: 3,194,411 members, 7,954,629 topics. Date: Saturday, 21 September 2024 at 03:02 AM |
Nairaland Forum / Science/Technology / Programming / Help Out With This Oracle Query (1410 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)
Nancy Fx: How To / Requirements For Andela's Bootcamp / Web Developer Needed
(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. 6 |