Home » SQL & PL/SQL » SQL & PL/SQL » Update Script - Increment Value (Database - 9i, OS - Win2003 Server)
Update Script - Increment Value [message #384938] Thu, 05 February 2009 16:12 Go to next message
rahul_bahulekar
Messages: 18
Registered: January 2007
Junior Member
I have one table which contain fileno and pageno, looks like –

Fileno Pageno
36590854 100
36590850 400
36590855 855
36590855 856
36590857 857
36590857 858
36590857 859
36590861 861
36590861 862

I want to update the records in such a way if fileno is distinct pageno column will get update to ‘1’ and is there are duplicate fileno page no will get update like increment number. Something like –

Fileno Pageno
36590854 1
36590850 1
36590855 1
36590855 2
36590857 1
36590857 2
36590857 3
36590861 1
36590861 2

After running below sql I am able to update the pageno only for distinct fileno, I facing problem if fileno is duplicate.

UPDATE PA000UCC90_DUMMY
SET PAGENO='1'
WHERE FILENO IN (SELECT FILENO FROM PA000UCC90_DUMMY GROUP BY FILENO HAVING COUNT(*)='1')

Thankx,
Rahul
Re: Update Script - Increment Value [message #384946 is a reply to message #384938] Thu, 05 February 2009 20:01 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>I facing problem if fileno is duplicate.


SET PAGENO = COUNT(*)
Re: Update Script - Increment Value [message #384963 is a reply to message #384946] Thu, 05 February 2009 22:53 Go to previous messageGo to next message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
Always provide DML and DDL for any query, this help us to respond quickly. Remember it for next time.

I have done it for you.

SQL> COLUMN FILE_NO FORMAT A10
SQL> COLUMN PAGE_NO FORMAT A10
SQL> CREATE TABLE pa000ucc90_dummy(file_no NUMBER(10),page_no NUMBER(5))
Table created.
SQL> INSERT INTO pa000ucc90_dummy
     VALUES (36590854, 100)
1 row created.
SQL> INSERT INTO pa000ucc90_dummy
     VALUES (36590850, 400)
1 row created.
SQL> INSERT INTO pa000ucc90_dummy
     VALUES (36590855, 855)
1 row created.
SQL> INSERT INTO pa000ucc90_dummy
     VALUES (36590855, 856)
1 row created.
SQL> INSERT INTO pa000ucc90_dummy
     VALUES (36590857, 857)
1 row created.
SQL> INSERT INTO pa000ucc90_dummy
     VALUES (36590857, 858)
1 row created.
SQL> INSERT INTO pa000ucc90_dummy
     VALUES (36590857, 859)
1 row created.
SQL> INSERT INTO pa000ucc90_dummy
     VALUES (36590861, 861)
1 row created.
SQL> INSERT INTO pa000ucc90_dummy
     VALUES (36590861, 862)
1 row created.
SQL> SELECT *
  FROM pa000ucc90_dummy

   FILE_NO    PAGE_NO
---------- ----------
  36590854        100
  36590850        400
  36590855        855
  36590855        856
  36590857        857
  36590857        858
  36590857        859
  36590861        861
  36590861        862

9 rows selected.
SQL> UPDATE pa000ucc90_dummy a
   SET page_no =
          (SELECT rn
             FROM (SELECT ROWID,
                          ROW_NUMBER () OVER (PARTITION BY file_no ORDER BY page_no)
                                                                           rn
                     FROM pa000ucc90_dummy) b
            WHERE a.ROWID = b.ROWID)
9 rows updated.
SQL> SELECT *
  FROM pa000ucc90_dummy

   FILE_NO    PAGE_NO
---------- ----------
  36590854          1
  36590850          1
  36590855          1
  36590855          2
  36590857          1
  36590857          2
  36590857          3
  36590861          1
  36590861          2

9 rows selected.


Thanks
Trivnedra
Re: Update Script - Increment Value [message #384972 is a reply to message #384963] Thu, 05 February 2009 23:39 Go to previous message
rahul_bahulekar
Messages: 18
Registered: January 2007
Junior Member
Thankx Trivnedra.

From onwards I will surely provide DML and DDL.

Thank you!!
Rahul.
Previous Topic: Need help in SQL statement
Next Topic: HR Api Problem. (Wrong Parameters)
Goto Forum:
  


Current Time: Sat Dec 10 20:48:34 CST 2016

Total time taken to generate the page: 0.24094 seconds