Update hierarchy table

Transact-SQL
1

I have Table contain
Code ParentCode
01 NULL
0101 01
0102 01
02 NULL
0201 02
0202 02
I need Update Table add "o" or more
the result
Code ParentCode
001 NULL
001001 001
001002 001
002 NULL
002001 002
002002 002

2

The quick and dirty way:

update yourtable
   set code=replace(code,'0','00')
      ,parentcode=replace(parentcode,'0','00')
;

Ofcause this should be done in test environment first!!!

3

thank you bitsmed
I know this update
but some value in column "code" not contain "0"

4

Please provide:

  • table definition(s) in the form of create statement(s)
  • sample data (with all variation types that is relevant for your case) in the form of insert statement(s)
  • expected output from the sample data you provide
  • description of the various rules you have
5

If you want to prepend every value with a zero '0':

UPDATE yourtable
SET code = concat('0', code)
  , parentcode = concat('0', code)

A better approach would probably be to select the data out of this table - adding the '0' - verifying the results are what you want - then rename the existing table to yourtable_old and the new table to yourtable. If you put this inside a transaction it will insure no updates/changes/inserts are performed during your process...which really means you need to do it after hours.