| Author |
 Topic  |
|
|
Indra_G
Starting Member
Indonesia
13 Posts |
 Posted - 05/04/2012 : 22:27:41
|
I found out I made an error of judgement that the digits that I used for the primary key of my table are too few. I need to add more digits to it. However I want to change the old data so that it reflect the new data. This is how it is:
My previous numbering system for my primary key is: P-000001, P-000002, and so on. It is now approaching P-999999! So I need to add extra digits to it, I planned to add 3 more to P-000XXXXXX. At the same time, we need to retain the old data in the table, but with new format.
I tried to update the old data by using this query:
"Update tblOrder Set Nomor = 'P-000%' Where Nomor = 'P-%'
But it does not want to work. Is there anyway that I can update multiple rows like this?
Thanks.
Software Akuntansi
http://intrikhidupit.blogspot.com/ |
|
|
Vinnie881
Flowing Fount of Yak Knowledge
USA
1202 Posts |
Posted - 05/04/2012 : 22:54:01
|
update a
set pk = replace(pk,'p-','p-000') + replace(pk,'p-','')
from
Mytable a
Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881 |
 |
|
|
Indra_G
Starting Member
Indonesia
13 Posts |
Posted - 05/05/2012 : 00:26:15
|
OK, I am sorry, but I am pretty new on this, so please be patient with me. If the table is tblOrder, and the field is Nomor. The query should be:
Update tblOrder set Nomor = replace(Nomor, 'p-', 'p-000') + replace(Nomor, 'p-',"") from Mytable tblOrder
Is that correct? I am sorry I am rather confused with the word Mytable. And, may I know why there are two replace for?
Software Toko|Software Akuntansi|Sistem Informasi Akuntansi |Sistem Informasi Manajemen |
|
|
|
Indra_G
Starting Member
Indonesia
13 Posts |
|
|
Vinnie881
Flowing Fount of Yak Knowledge
USA
1202 Posts |
Posted - 05/05/2012 : 10:10:14
|
Update a set Nomor = replace(Nomor, 'p-', 'p-000') + replace(Nomor, 'p-',"") from tblOrder a
Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881 |
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 05/05/2012 : 10:30:48
|
quote:
Originally posted by Vinnie881
Update a set Nomor = replace(Nomor, 'p-', 'p-000') + replace(Nomor, 'p-',"") from tblOrder a
Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881
Vinnie, I have the same question that Indra was asking - I didn't follow the reason for the second replace. Wouldn't this be sufficient? Update tblOrder set Nomor = replace(Nomor, 'p-', 'p-000'); |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47040 Posts |
Posted - 05/05/2012 : 10:45:50
|
update a
set pk = stuff(pk,3,0,'000')
from
Mytable a
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
Vinnie881
Flowing Fount of Yak Knowledge
USA
1202 Posts |
Posted - 05/05/2012 : 14:40:15
|
No need for second replace. It was a mistake that probably is relayed to running on 4 hours of sleep for the last few days.
Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881 |
|
|
|
Indra_G
Starting Member
Indonesia
13 Posts |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47040 Posts |
Posted - 05/07/2012 : 17:13:41
|
welcome
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
How can I update multiple rows at the same time?
