Home » SQL & PL/SQL » SQL & PL/SQL » UPDATE MANY ROWS OF DATA (SQL DEVELOPER)
icon4.gif  UPDATE MANY ROWS OF DATA [message #349742] Mon, 22 September 2008 23:31 Go to next message
fadhzone
Messages: 61
Registered: April 2008
Member
Hi All,

I need to update a sequence data (Gift Voucher).
The Wrong entered GV no is between 104491001 to 104496000.

Now I want to update the GV No;
100491001 to 100496000.

my question is; How could I update the whole GV no. using script without update it one by one?

Thanks.
Re: UPDATE MANY ROWS OF DATA [message #349743 is a reply to message #349742] Mon, 22 September 2008 23:37 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Can you read you question once more ? Do you think its complete ?

Anyway ,
It is just update statement.

UPDATE <>
SET   <> =  <> 
WHERE ....



Thumbs Up
Rajuvan.
Re: UPDATE MANY ROWS OF DATA [message #349748 is a reply to message #349742] Tue, 23 September 2008 00:10 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
You have this boundaries: 104.491.001 and 104.496.000
Would like to have      : 100.491.001 and 100.496.000 
-----------------------------------------------------
Difference                  4.000.000       4.000.000
An award-winning question: how much do you have to subtract from the original GV value, which is between 104.491.001 and 104.496.000 (WHERE gv BETWEEN ...), to set it to a new value (SET gv = gv - ...)?
Re: UPDATE MANY ROWS OF DATA [message #349757 is a reply to message #349743] Tue, 23 September 2008 00:55 Go to previous messageGo to next message
fadhzone
Messages: 61
Registered: April 2008
Member
yes i know it's only updating..

but...
the sequence like this

GV_NO
------
10449100113
10449100245
10449100369
...
...
104496000

I want to update the 3rd no of the sequence from 4 to 0...


Re: UPDATE MANY ROWS OF DATA [message #349763 is a reply to message #349757] Tue, 23 September 2008 01:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I want to update the 3rd no of the sequence from 4 to 0...

So do it!
Or maybe don't you know SUBSTR? or "-"? (depending on your colymn data type)

Regards
Michel

[Updated on: Tue, 23 September 2008 01:13]

Report message to a moderator

Re: UPDATE MANY ROWS OF DATA [message #349767 is a reply to message #349763] Tue, 23 September 2008 01:20 Go to previous messageGo to next message
fadhzone
Messages: 61
Registered: April 2008
Member
OK..

I tried it..but there is an error

UPDATE GV_DETAIL
SET SUBSTR(GV_NO,3,1) = 0
WHERE STORE_CD = 1044
AND RELEASED_REF_NO = 497
AND ACTIVATED_REF_NO = 493
AND GV_TYPE = 10;

the error is: ORA-00927 : missing equal sign Sad
Re: UPDATE MANY ROWS OF DATA [message #349770 is a reply to message #349742] Tue, 23 September 2008 01:30 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

You can try somewhat like this

UPDATE + SUBSTR

Thumbs Up,
Rajuvan.
Re: UPDATE MANY ROWS OF DATA [message #349773 is a reply to message #349742] Tue, 23 September 2008 01:33 Go to previous messageGo to next message
idris.ali
Messages: 34
Registered: June 2008
Location: Hyderabad
Member
use something like

SELECT SubStr('104491001 ',0,2)||'0'||SubStr('104491001 ',4) FROM dual
Re: UPDATE MANY ROWS OF DATA [message #349775 is a reply to message #349773] Tue, 23 September 2008 01:40 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
What datatype is this GV column? If it is numeric, you should not us string manipulation functions, but simple arithmetic. (This is what LittleFoot already hinted at several posts ago)
Re: UPDATE MANY ROWS OF DATA [message #349777 is a reply to message #349775] Tue, 23 September 2008 01:46 Go to previous messageGo to next message
fadhzone
Messages: 61
Registered: April 2008
Member
it's a varchar2 datatype
Re: UPDATE MANY ROWS OF DATA [message #349779 is a reply to message #349742] Tue, 23 September 2008 01:56 Go to previous message
fadhzone
Messages: 61
Registered: April 2008
Member
Finally, I did it using below script.

UPDATE GV_DETAIL
SET GV_NO = SUBSTR(GV_NO,1,2)||'0'||SUBSTR(GV_NO,4,13)
WHERE STORE_CD = 1044
AND RELEASED_REF_NO = 497
AND ACTIVATED_REF_NO = 493
AND GV_TYPE = 10
AND SUBSTR(GV_NO,3,1) = 4;

Thank you very much; rajavu1,littlefoot,michael,idris,frank..

your ideas really helpful..
Previous Topic: employee salary in words
Next Topic: Update table set value => ORA-00904
Goto Forum:
  


Current Time: Sat Dec 03 15:50:11 CST 2016

Total time taken to generate the page: 0.07657 seconds