|
|||||||||
|
|||||||||
| |||||||||
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
Dev Shed Forums Sponsor:
|
|
|
|
#1
October 26th, 2004, 09:23 AM
|
|||
|
|||
Add 0 to excisting number
Ok,
This should be simple but it must be to early..... I'm trying to add a 0 to some columns in my database So if my ProductID = 12345 I want to make it = 012345 and I want to do this for the hole productID column Thanks for the help!
|
|
#2
October 26th, 2004, 10:42 AM
|
||||
|
||||
|
if productid is a character datatype, then use this:
Code:
update products set productid = '0' + productid but if productid is numeric, then the only way you'll ever see the leading zero is if you use '0' + cast(productid as varchar) in every SELECT statement Last edited by r937 : October 26th, 2004 at 10:45 AM.
|
|
#5
October 26th, 2004, 11:33 AM
|
|||
|
|||
|
ok help again
Server: Msg 2601, Level 14, State 3, Line 1
Cannot insert duplicate key row in object 't_productID' with unique index 'idx_productID'. The statement has been terminated. So I need to do if excits to drop it or just plan out skip that row, and i will drop it later
|
|
#6
October 26th, 2004, 12:27 PM
|
||||
|
||||
|
Code:
update products as x set productid = '0' + productid where not exists ( select '0' + productid from products where productid = x.productid ) or something like that
|
|
#8
October 26th, 2004, 01:50 PM
|
||||
|
||||
|
yeah, sorry, you have to use FROM syntax in order to use an alias
please try this: Code:
update products
set productid = '0' + t1.productid
from products t1
left outer
join products t2
on t1.productid = '0' + t2.productid
where t2.productid is null
|
|
| Viewing: Dev Shed Forums > Databases > MS SQL Development > Add 0 to excisting number |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
MMMmmm
Linear Mode
