sql to edit all partial data in all rows of database

[bschwarz]

I've been working on a backend for the company I work for where by I have all the clients, along with client numbers.

Project numbers are generated by client number+single year number (4)+job number.

So anyway, while entering the data into the database I made a mistake, a direct result of this is that all the client numbers are WRONG!

this causes all project numbers to be wrong also!

so I need a line of sql where I can change for example the client number "01401" to "00401" (shift the first two digits down one number.

How the hell can I do this? Help!

[mrhappiness]

try

UPDATE tablename
SET projectnumber = CONCAT(
    LPAD(SUBSTRING(projectnumber, 1, 2)  - 1, 2, '0'),
    SUBSTRING( projectnumber, 2, LENGTH(projectnumber)  - 2)
  )

this should work i guess

btw: you should think about using three columns for this and generate the projectnumber with your select (CONCAT my be helpful)

[olaf]

Works your statement in Mysql 3.23 too?

[mrhappiness]

i think so

why should it not work?

i tested it with a select and it did what i expected it to do

[olaf]

I thought that CONCAT is new in ver 4, but i'm wrong...

🙂