Results 1 to 11 of 11
  1. #1
    Join Date
    Oct 2008
    Posts
    46

    Unanswered: How to update data?

    Hi,

    Please, anybody can write script for updating Rep_No field.
    I have tbl1 with ID, RepID and Rep_No fields with following data

    ID RepID Rep_No
    1 0 Null
    2 0 Null
    3 3 Null
    4 3 Null
    4 4 Null
    5 4 Null
    6 6 Null
    7 4 Null

    I have to update Rep_No looks like:

    ID RepID Rep_No
    1 0 1 of 1
    2 0 1 of 1
    3 3 1 of 2
    4 3 2 of 2
    4 4 1 of 3
    5 4 2 of 3
    6 6 1 of 1
    7 4 3 of 3
    Last edited by rajan142; 12-04-08 at 22:21.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    DECLARE @f  TABLE (
       ID		CHAR(1)
    ,  RepID	NUMERIC(1)
    ,  Rep_No	NVARCHAR(8)
       )
    
    INSERT INTO @f (ID, RepID)
       SELECT       1, 0
       UNION SELECT 2, 0
       UNION SELECT 3, 3
       UNION SELECT 4, 3
       UNION SELECT 4, 4
       UNION SELECT 5, 4
       UNION SELECT 6, 6
       UNION SELECT 7, 4
    
    SELECT * FROM @f
    
    UPDATE @f
       SET Rep_No = CASE
          WHEN '1' = ID AND 0 = RepId THEN '1 of 1'
          WHEN '2' = ID AND 0 = RepId THEN '1 of 1'
          WHEN '3' = ID AND 3 = RepId THEN '1 of 2'
          WHEN '4' = ID AND 3 = RepId THEN '2 of 2'
          WHEN '4' = ID AND 4 = RepId THEN '1 of 3'
          WHEN '5' = ID AND 4 = RepId THEN '2 of 3'
          WHEN '6' = ID AND 6 = RepId THEN '1 of 1'
          WHEN '7' = ID AND 4 = RepId THEN '3 of 3'
       END
    
    SELECT * FROM @f
    -PatP

  3. #3
    Join Date
    Oct 2008
    Posts
    46
    Thanks for replay.
    There is alredy data in tbl1 more than 95000 so i can not use case for each.
    And RepID could be more than 100 for one ID.

    Anybody can help me?

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    No need to store the value if you're on 2005
    Code:
    SELECT a.id
         , a.repid
         , a.x
         , b.y
         , Convert(varchar, a.x) + ' of ' + Convert(varchar, b.y) As [rep_no]
    FROM   (
            SELECT id
                 , repid
                 , Row_Number() OVER (PARTITION BY repid ORDER BY id) As [x]
            FROM   @f
           ) As [a]
     INNER
      JOIN (
            SELECT repid
                 , Count(*) As [y]
            FROM   @f
            GROUP
                BY repid
           ) As [b]
        ON a.repid = b.repid
    ORDER
        BY id
    George
    Home | Blog

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    You don't actually need two subqueries either...
    Code:
    SELECT a.id
         , a.repid
         , Convert(varchar, Row_Number() OVER (PARTITION BY a.repid ORDER BY a.id)) + ' of ' + Convert(varchar, b.y) As [rep_no]
    FROM   @f As [a]
     INNER
      JOIN (
            SELECT repid
                 , Count(*) As [y]
            FROM   @f
            GROUP
                BY repid
           ) As [b]
        ON a.repid = b.repid
    ORDER
        BY id
    George
    Home | Blog

  6. #6
    Join Date
    Oct 2008
    Posts
    46
    how do i update rep_no?

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Why bother updating when you can calculate it so easily?

    Every time you add a new record, you're going to have to recalculate a bunch of records! So why bother with the hassle and just create a view of the code provided above.
    George
    Home | Blog

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    George, won't you need an additional CASE statement to handle the exception for RepID 0?

    -PatP

  9. #9
    Join Date
    Apr 2007
    Posts
    183
    Code:
    UPDATE	s
    SET	s.rep_no = CASE repid
    			when 0 then '1 of 1'
    			else cast(f as varchar(12)) + ' of ' + cast(e as varchar(12))
    		end
    FROM	(
    		SELECT	rep_no,
    			repid,
    			count(*) over (partition by repid) as e,
    			row_number() OVER (partition by repid order by id) AS f
    		FROM	tbl1
    	) AS s

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by Pat Phelan
    George, won't you need an additional CASE statement to handle the exception for RepID 0?

    -PatP
    Come again? I don't see how you can have 0..?
    George
    Home | Blog

  11. #11
    Join Date
    Oct 2008
    Posts
    46
    thanks Peso.
    It works.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •