Home » SQL & PL/SQL » SQL & PL/SQL » Updating Distinct Values
Updating Distinct Values [message #349927] Tue, 23 September 2008 09:05 Go to next message
NYSIF Steve
Messages: 2
Registered: August 2008
Location: Albany, NY
Junior Member
I am trying to develope a PL/SQL data validation program. Below is the query I am having issues with. One sequence number should be populated for all the records having same receipt date and CMS check category. But the program is populating one trx id for each row. Any help would be appreciated. Thank you.

UPDATE nysfms.nysif_cms_transactions ncmt
        SET cm_trx_id = NYSIF_CE_MF_RECEIPTS_S.nextval
      WHERE cms_check_category = ncmt.cms_check_category
        AND receipt_date = ncmt.receipt_date
        AND cm_trx_id is NULL
        AND record_type = 'N'
        AND cms_check_category NOT IN ('ATF-NG', 'DBF-NG', 'WCF-NG')
        AND status = 'IMPORTED' or status ='ERRORED';


[Updated on: Tue, 23 September 2008 09:10]

Report message to a moderator

Re: Updating Distinct Values [message #349939 is a reply to message #349927] Tue, 23 September 2008 09:38 Go to previous messageGo to next message
chakradhar.adhikari
Messages: 5
Registered: September 2008
Junior Member
Hi,

The above statement update only one row at a time. Inorder to update each row in a table you must specify one more condition with exist clause.

Example:

I am trying to update each row in temp_student table as follows. This one i took as an example so you have to convert this one as of your reqirement.

UPDATE temp_student a
SET sno = sno.NEXTVAL
WHERE EXISTS (SELECT 1
FROM temp_student b
WHERE a.sno = b.sno)



Re: Updating Distinct Values [message #349952 is a reply to message #349927] Tue, 23 September 2008 10:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Afaik, you have to do it in 2 statements: first get the sequence value in a variable then use "currval" in update. You can't use the sequence "nextval" in update to get only one value for all rows.
SQL> select s.nextval from dual;
   NEXTVAL
----------
         8

1 row selected.

SQL> update t set col = s.currval;

2 rows updated.

SQL> select * from t;
        ID        COL
---------- ----------
         0          8
         1          8

2 rows selected.

Regards
Michel
Re: Updating Distinct Values [message #349967 is a reply to message #349939] Tue, 23 September 2008 11:01 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@chakradhar.adhikari,

I believe you didn't understand the requirement asked by @NYSIF Steve properly.
Quote:

One sequence number should be populated for all the records having same receipt date and CMS check category.



Your query will also populate different sequence number to the set of rows. Simple example consider you have a table like:
DROP TABLE TEMP_TABLE1 CASCADE CONSTRAINTS;

CREATE TABLE TEMP_TABLE1
(
  COL_1  NUMBER,
  COL_2  VARCHAR2(5)
)

I have sample data as follows:
Insert into TEMP_TABLE1 (COL_1, COL_2)
 Values  (1, 'A');
Insert into TEMP_TABLE1 (COL_1, COL_2)
 Values  (4, 'A');
Insert into TEMP_TABLE1 (COL_1, COL_2)
 Values  (5, 'A');
Insert into TEMP_TABLE1 (COL_1, COL_2)
 Values  (2, 'B');
Insert into TEMP_TABLE1 (COL_1, COL_2)
 Values  (6, 'B');
Insert into TEMP_TABLE1 (COL_1, COL_2)
 Values  (3, 'C');
COMMIT;

The requirement will be as follows(imagining I have a sequence whose nextval will be 10 and I am updating COL_1 with it):
  COL_1     COL_2
  ------   -------
      10   A
      10   A
      10   A
      11   B
      11   B
      12   C     

Your query will give the following output:
  COL_1     COL_2
  ------   -------
      10   A
      11   A
      12   A
      13   B
      14   B
      15   C     

Moreover, there is no need for the following condition in your mentioned query.
WHERE EXISTS (SELECT 1
FROM temp_student b
WHERE a.sno = b.sno)

Without the above condition you get the same output. Try the below query along with your query and see if you find any difference.
UPDATE temp_student a
SET sno = sno.NEXTVAL


Finally please read OraFaq Forum Guide before posting.

@NYSIF Steve,
Instead of using nextval directly in an update statement, store the nextval of the sequence in a variable and use it in your update statement.

Hope this helps.
[Guess this was already answered by @Michel. Didn't see that post]

Regards,
Jo

[Updated on: Tue, 23 September 2008 11:03]

Report message to a moderator

Re: Updating Distinct Values [message #350067 is a reply to message #349927] Wed, 24 September 2008 00:27 Go to previous message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

One more point to Note .


WHERE cms_check_category = ncmt.cms_check_category
        AND receipt_date = ncmt.receipt_date
        AND cm_trx_id is NULL
        AND record_type = 'N'
        AND cms_check_category NOT IN ('ATF-NG', 'DBF-NG', 'WCF-NG')
        AND status = 'IMPORTED' or status ='ERRORED'


Do you really intended to use

AND status = 'IMPORTED' or status ='ERRORED'


instead of

AND (status = 'IMPORTED' or status ='ERRORED')
??

I hope you understand the difference well.

Thumbs Up
Rajuvan.
Previous Topic: Updating from a select
Next Topic: Subselect union
Goto Forum:
  


Current Time: Mon Dec 05 15:02:23 CST 2016

Total time taken to generate the page: 0.09410 seconds