Rename SQL Server 2005 Row Values

Question

I want to rename several values on my table.

I just want to rename multi rows in a column of a table in my database :

SJ.10.06.000001
SJ.10.06.000002
SJ.10.06.000003
SA.10.06.000001
SB.10.06.000002

etc into this value :

SJ.09.06.000001
SJ.09.06.000002
SJ.09.06.000003
SA.09.06.000001
SB.09.06.000002

My SQL :

Update dbo.Deposito 
set nomor sj.09... 
where no rekening sj.10...

and I've got this :

Update dbo.Deposito 
set nomor sj.09... 
where no rekening sj.10...

Error

[Err] 42000 - [SQL Server]Incorrect syntax near 'sj'.

What is the layout of the table? Is this a single value or multiple values in different columns? — Gordon Linoff, Aug 25 '14 at 12:05
`set nomor sj.09... where no rekening sj.10` That is not in the least valid sql syntax — Tim Schmelter, Aug 25 '14 at 12:06

score 1 · Accepted Answer · answered Aug 25 '14 at 12:22

1

try this....

UPDATE dbo.Deposito 
 SET nomor =  REPLACE(nomor, '.10.', '.09.')
WHERE SUBSTRING(nomor, 4, 2) = '10'

answered Aug 25 '14 at 12:22

M.Ali

Alex Szabo · Answer 2 · 2014-08-25T12:24:25.750

0

I think you can do this with substring too, but I could not remember the correct syntax, so I think you should try this.

It updates the values based on the 3rd and 4th characters in your table, so it exchanges 10 to 9, if that's what you are after.

There are a lot of ways to achieve this, it's just one of them.

update

I have tested this locally and it works.

UPDATE
    dbo.Deposito
SET
    nomor = REPLACE(nomor,'.10.','.09.')
WHERE
    substring(nomor,4,2) = '10'

edited Aug 25 '14 at 12:24

answered Aug 25 '14 at 12:11

Alex Szabo

When I try yours, I've got this result [Err] 42000 - [SQL Server]Incorrect syntax near the keyword 'RIGHT'. – Danang Wahyu Aug 25 '14 at 12:19
it's the same with M. Ali answer. But, thank you I appreciate for your help ^_^ – Danang Wahyu Aug 25 '14 at 12:37

2 Answers2