locked
Converting SELECT Statement into UPDATE RRS feed

  • Question

  • Hi All,

    Running SQL Server 2008 R2.  I have the following SELECT query, which is returning the desired results.

    SELECT DISTINCT
        [x].[AccountNo],
        [x].[AvgAccountLen],
        CASE
            WHEN LEN([AccountNo]) > 6 THEN LEFT([AccountNo], 6)
    	ELSE [AccountNo] + REPLICATE('0', [AvgAccountLen] - LEN([AccountNo]))
        END AS [NewAccountNo]
    FROM
        (
        SELECT DISTINCT
            [AccountNo],
    	(
    	SELECT TOP 1
    	    LEN([AccountNo])
    	FROM
    	    [dbo].[Table]
    	WHERE
    	    [AccountNo] > 0
    	GROUP BY
    	    [AccountNo]
    	ORDER BY
    	    COUNT(*) DESC
    	) AS [AvgAccountLen]
        FROM
    	[dbo].[Table]
        ) AS [x]
    WHERE
        LEN([AccountNo]) <> [AvgAccountLen]

    Below are results, which again are what I'm looking for.

    AccountNo    AvgAccountLen    NewAccountNo
    ---------    -------------    ------------
    4200         6                420000
    4250         6                425000
    42000        6                420000
    4030         6                403000
    4460         6                446000
    4250000      6                425000
    4520000      6                452000

    Long story short is that I've been left to clean up a partially-completed task.  I need to conduct an update on Table that pads (or trims) the account numbers accordingly.  Further, this process affects multiple entities which is why I can't simply use a static pad/trim value of 6 (this particular entity returns 6, there could be other entities with 4, 8, etc.).  AvgAccountLen may not be the most appropriate column name either - it's a representation of the most frequently-occurring value length (I have already confirmed that the result returned for this value is correct in each entity).  How would I go about writing a UPDATE statement to accomplish this?

    Any help is greatly appreciated!

    Best Regards

    Brad

    Wednesday, April 8, 2015 9:01 PM

Answers

  • Can you provide your example data as a table to compliment your expected result?

    I'm thinking something like this may help:

    DECLARE @accounts TABLE (accountNo INT, avgAccountLen INT, newAccountNumber INT)
    INSERT INTO @accounts (accountNo, avgAccountLen) VALUES
    (4200   ,      6),
    (4250   ,      6),
    (42000  ,      6),
    (4030   ,      6),
    (4460   ,      6),
    (4250000,      6),
    (4520000,      6)
    
    UPDATE @accounts
    SET newAccountNumber = LEFT(CAST(accountNo AS VARCHAR)+REPLICATE('0',avgAccountLen),avgAccountLen)
      FROM @accounts
    
    SELECT *
      FROM @accounts


    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question.

    • Proposed as answer by Charlie Liao Friday, April 10, 2015 7:21 AM
    • Marked as answer by Charlie Liao Monday, April 27, 2015 3:18 AM
    Wednesday, April 8, 2015 10:03 PM

All replies

  • Simple

    If < 10000 then multiply by 100

    else if < 100000 then multiply by 10


    jdweng

    Wednesday, April 8, 2015 9:19 PM
  • Hi Joel,

    I don't think this would work due to the fact that not all account numbers to be padded/trimmed are shorter in length than AvgAccountNo.  The last two rows in my example resultset wouldn't fall into either scenario you mentioned above, as they are both seven characters in length.

    Thanks

    Brad

    Wednesday, April 8, 2015 9:54 PM
  • Can you provide your example data as a table to compliment your expected result?

    I'm thinking something like this may help:

    DECLARE @accounts TABLE (accountNo INT, avgAccountLen INT, newAccountNumber INT)
    INSERT INTO @accounts (accountNo, avgAccountLen) VALUES
    (4200   ,      6),
    (4250   ,      6),
    (42000  ,      6),
    (4030   ,      6),
    (4460   ,      6),
    (4250000,      6),
    (4520000,      6)
    
    UPDATE @accounts
    SET newAccountNumber = LEFT(CAST(accountNo AS VARCHAR)+REPLICATE('0',avgAccountLen),avgAccountLen)
      FROM @accounts
    
    SELECT *
      FROM @accounts


    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question.

    • Proposed as answer by Charlie Liao Friday, April 10, 2015 7:21 AM
    • Marked as answer by Charlie Liao Monday, April 27, 2015 3:18 AM
    Wednesday, April 8, 2015 10:03 PM
  • select Left(AccountNo +'00000',6) ...
    Wednesday, April 8, 2015 10:04 PM