Transaction to update multipel values in a table
-
Hi There, I don't really use sql so this can be an stupid questoin but, say ive got a column called accountNumber with the following values: 55123 55124 55124 ...etc and i want to change all the values (5000+ entries) to a concatenation of '000' + the current accountNumber. i.e. 00055123;00055124;00055125 how will i achieve this? Regards and thanks
-
Hi There, I don't really use sql so this can be an stupid questoin but, say ive got a column called accountNumber with the following values: 55123 55124 55124 ...etc and i want to change all the values (5000+ entries) to a concatenation of '000' + the current accountNumber. i.e. 00055123;00055124;00055125 how will i achieve this? Regards and thanks
here it is
update tablename set accountNumber = '000'+accountNumber
I Love T-SQL "Don't torture yourself,let the life to do it for you." If my post helps you kindly save my time by voting my post. www.aktualiteti.com
-
Hi There, I don't really use sql so this can be an stupid questoin but, say ive got a column called accountNumber with the following values: 55123 55124 55124 ...etc and i want to change all the values (5000+ entries) to a concatenation of '000' + the current accountNumber. i.e. 00055123;00055124;00055125 how will i achieve this? Regards and thanks
What data type is accountNumber stored in? What database are you using?
I have CDO, it's OCD with the letters in the right order; just as they ruddy well should be
Forgive your enemies - it messes with their heads
-
What data type is accountNumber stored in? What database are you using?
I have CDO, it's OCD with the letters in the right order; just as they ruddy well should be
Forgive your enemies - it messes with their heads
Data type is string and it is a sql express database. Also, i cannot update the etries that already have a '000' prefix so i can do something like :
UPDATE TableName
SET AccountNumber = '000' + AccountNumber
WHERE AccountNumber NOT LIKE '000%'Correct?
-
Data type is string and it is a sql express database. Also, i cannot update the etries that already have a '000' prefix so i can do something like :
UPDATE TableName
SET AccountNumber = '000' + AccountNumber
WHERE AccountNumber NOT LIKE '000%'Correct?
What you need to do is use the LPAD function in SQL Server to pad the number to a set size. So, suppose you want to have it padded out to 10 characters, you'd use
UPDATE TableName SET AccountNumber = LPAD(AccountNumber, 10, '0')
I have CDO, it's OCD with the letters in the right order; just as they ruddy well should be
Forgive your enemies - it messes with their heads