Home » SQL & PL/SQL » SQL & PL/SQL » Update, last digit of a number column (Oracle 10g)
Update, last digit of a number column [message #349711] Mon, 22 September 2008 16:38 Go to next message
theladyd
Messages: 13
Registered: September 2008
Junior Member
I'm trying to update a number column. I want to change the last digit to equal another column by dropping the last digit and concatenating the field2.
Example

field1......field2.....Updated Field1
1111..........0...........1110
1112..........1...........1111
11............0...........10
11............1...........11
555121........0...........555120
555123........1...........555121
777456........0...........777450
77777771......0...........77777770
77777771......1...........77777771

The field1 length is 10 digits and my largest number is 8 digits, so I tried the below sql using either substr(field1,1,7) or substr(field1,1,9), but it doesn't work for all the rows. I'm trying to update a little more than 500,000 rows. Any and all help is appreciated.

UPDATE TABLENAME
SET
FIELD1 = SUBSTR(FIELD1,1,9)||FIELD2;
Re: Update, last digit of a number column [message #349717 is a reply to message #349711] Mon, 22 September 2008 19:12 Go to previous messageGo to next message
vesile_taskiran
Messages: 66
Registered: August 2008
Location: Turkey
Member
its not a good solution but it ll o exactly what you want : )

select reverse(replace(reverse(TO_CHAR(field1)),1,field2))
Re: Update, last digit of a number column [message #349733 is a reply to message #349711] Mon, 22 September 2008 21:03 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
SQL> select field1
  2       , field2
  3       , to_number(regexp_replace(field1, '.$')||field2)  "updated field1"
  4  from t1;

    FIELD1     FIELD2 updated field1
---------- ---------- --------------
      1112          1           1111
        11          0             10
        11          1             11
    555121          0         555120
    555123          1         555121
    777456          0         777450
  77777771          0       77777770
  77777771          1       77777771

8 rows selected.
Re: Update, last digit of a number column [message #349747 is a reply to message #349711] Tue, 23 September 2008 00:05 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Well, SUBSTR could also be helpful; you'd only have to use it properly.
SQL> select
  2    field1,
  3    field2,
  4    to_number(substr(field1, 1, length(field1) - 1) || field2) result
  5  from test;

    FIELD1     FIELD2     RESULT
---------- ---------- ----------
      1111          0       1110
      1112          1       1111
    555121          0     555120
        11          0         10
        11          1         11
    555123          1     555121
    777456          0     777450

7 rows selected.

SQL>
Re: Update, last digit of a number column [message #349792 is a reply to message #349711] Tue, 23 September 2008 02:42 Go to previous messageGo to next message
Alessandro Rossi
Messages: 166
Registered: September 2008
Location: Rome
Senior Member
What about a simple
trunc(field1/10)*10+field2


Bye Alessandro
Re: Update, last digit of a number column [message #349961 is a reply to message #349792] Tue, 23 September 2008 10:43 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Nice option Alessandro.
Re: Update, last digit of a number column [message #350009 is a reply to message #349961] Tue, 23 September 2008 14:02 Go to previous message
theladyd
Messages: 13
Registered: September 2008
Junior Member
Thanks all for the quick response. I really appreciate it.
Alessandro, your suggestion worked well.
Previous Topic: Password encryption with high security
Next Topic: Updating from a select
Goto Forum:
  


Current Time: Wed Dec 07 20:16:36 CST 2016

Total time taken to generate the page: 0.10777 seconds