how to consolidate Update Statement into one statement

Question

Tools: SQL Server 2008

Environment: Windows Vista

Problem: I am trying to consolidate the following code block into one statement. If the length of ID  is 8 then add 0 to the ID. If the length of the ID is 7 then add '00' and so forth.Knowing that the code works perfect but it I want to put the 4 block of update SQL code into one. I am curious how it would be done. I would appreciate your input

Answer 1

Try:

UPDATE T
SET  ID = RIGHT('000000000' + cast(ID as varchar(10)),9)
FROM dbo.tbl_ind_ID T 

Inner join dbo.tbl_individual c on T.pn_id=c.pn_id
inner join dbo.tbl_ind_scr d on c.pn_id = d.pn_id
where d.fac_id='6'

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 2

Many thanks for the prompt response.

I also wonder how the code would be if O want to reverse of the code. (deleting the zero of the beginning of the ID) for example, say I have an ID = 000012345 and I want to truncate zero which it would be 00012345.

UPDATE T
SET  ID = left('00000000 ' + cast(ID as varchar(10)),9)
FROM X T 

Inner join XX c on T.x=c.pn_id
inner join XX d on c.x = d.pn_id
where d.fac_id='6'

Answer 3

One possibility:

UPDATE T
SET  ID = 
case when ID LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' then cast(cast(ID as bigint) as varchar(9)) else ID end

FROM dbo.tbl_ind_ID T 

Inner join dbo.tbl_individual c on T.pn_id=c.pn_id
inner join dbo.tbl_ind_scr d on c.pn_id = d.pn_id
where d.fac_id='6'

I assume that ID is a varchar(9) column and you want to remove leading 0s.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 4

>> If the length of foo_id is 8 then add 0 to the foo_id. If the length of the foo_id is 7 then add '00' and so forth. Knowing that the code works perfect but it I want to put the 4 block of update SQL code into one.<<

I hope that the real code does not use a magical universal “foo_id” and those silly “tbl-” affixes. What real SQL programers do is protect the data base with DDL. You you 1950's COBOL patch up data after the fact.

You also did not known that “foo_id IN (SELECT DISTINCT ..)” does not need the DISTINCT. You are doing the wrong thing and writign bad code to do it. 

CREATE TABLE Foobar
(foo_id CHAR(9) NOT NULL
   CHECK (foo_id 
         LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'),
 etc);

Now you have no problem and can spend some time beating up the moron who failed to create a valid schema. His termination will improve your enterprise more than fixing his kludges one at a time :)

Since you did not follow Netiquette, we have no way to figure the WHERE clause in your code means. To add leading zeros ot a string try this version. 

UPDATE Foobar
 SET foo_id 
  = SUBSTRING 
  (REVERSE 
   (REVERSE (foo_id) + '0000000000'), 1, 9);

The logic is simple. Flip the string, concatenate the zeros, flip it back and then pull out the nine rightmost characters. This is all nested function calls so it is really fast and requires no other logic. 

---

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

Answer 5

UPDATE T
SET  ID = RIGHT('000000000' + RTRIM(10)),9)
FROM dbo.tbl_ind_ID T 
Inner join dbo.tbl_xyz c on T.pn_id=c.pn_id
inner join dbo.tbl_ind on c.pn_id = d.pn_id
where d.fac_id='6'