| Author |
Topic |
|
Indra_G
Starting Member
13 Posts |
 Posted - 2012-05-04 : 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 Akuntansihttp://intrikhidupit.blogspot.com/ |
|
|
Vinnie881
Master Smack Fu Yak Hacker
1231 Posts |
Posted - 2012-05-04 : 22:54:01
|
update aset 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
13 Posts |
Posted - 2012-05-05 : 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 tblOrderIs 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
13 Posts |
|
|
Vinnie881
Master Smack Fu Yak Hacker
1231 Posts |
Posted - 2012-05-05 : 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
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-05-05 : 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
52326 Posts |
Posted - 2012-05-05 : 10:45:50
|
| [code]update aset pk = stuff(pk,3,0,'000')from Mytable a[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
Vinnie881
Master Smack Fu Yak Hacker
1231 Posts |
Posted - 2012-05-05 : 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
13 Posts |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-05-07 : 17:13:41
|
| welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
How can I update multiple rows at the same time?