Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2003
    Posts
    60

    Unanswered: Help Simply the Update Statement

    Hello all,

    I need some help in simplyfying the following update statement -

    update table <table_a> set <col_1> = NULL where <col_1> = 'N/A'
    update table <table_a> set <col_2> = NULL where <col_2> = 'N/A'
    update table <table_a> set <col_3> = NULL where <col_3> = 'N/A'
    update table <table_a> set <col_4> = NULL where <col_4> = 'N/A'
    update table <table_a> set <col_5> = NULL where <col_5> = 'N/A'
    update table <table_a> set <col_6> = NULL where <col_6> = 'N/A'
    update table <table_a> set <col_7> = NULL where <col_7> = 'N/A'
    ....and there are 73 columns

    Anyway I can create a loop or array and store the column name as a parameter and then pass it to the update statement?

    Thanks in advance,
    Saurav

  2. #2
    Join Date
    May 2007
    Location
    somewhere in dbforums
    Posts
    221
    Put this in a SP and keep @table as parameter

    DECLARE @COLUMN VARCHAR(100)
    DECLARE @TABLE VARCHAR(100)

    CREATE TABLE #TEMP_TABLE
    (
    COLUMNNAME VARCHAR(100)
    )

    SET @TABLE = 'PRODUCTS'

    INSERT INTO #TEMP_TABLE

    SELECT A.NAME FROM
    SYSCOLUMNS AS A
    INNER JOIN
    SYSOBJECTS AS B
    ON
    A.ID = B.ID
    WHERE
    B.NAME = @TABLE --TABLE NAME


    WHILE ((SELECT COUNT(*) FROM #TEMP_TABLE) > 0)
    BEGIN

    SELECT TOP 1 @COLUMN = COLUMNNAME
    FROM #TEMP_TABLE

    EXEC('UPDATE' + @TABLENAME + ' SET ' + @COLUMN + ' = NULL WHERE ' + @COLUMN + ' = ''N/A''')

    DELETE FROM #TEMP_TABLE WHERE COLUMNNAME = @COLUMN

    END

    DROP #TEMP_TABLE
    Last edited by nick.ncs; 08-03-07 at 06:19.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    There's rarely need to send table names to sprocs for dynamic sql, and this isn't one of those times in any case. It's all on the same table.

    Simpler:
    Code:
    update table <table_a> 
    set <col_1> = NULLIF(<col_1>, 'N/A')
    , <col_2> = NULLIF(<col_2>, 'N/A')
    , <col_3> = NULLIF(<col_3>, 'N/A')
    .....
    FROM <table_a>
    WHERE <col_1> = 'N/A' 
    OR <col_2> = 'N/A' 
    OR <col_3> = 'N/A'....
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Aug 2003
    Posts
    60

    Thank you!

    You rock, Blindman. Thanks you!!

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by sajmera
    You rock, Blindman. Thanks you!!
    I'll be sure to tell him

    --blindman is quoted in my sig - he ain't me and he will be pretty appalled when he sees this.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Aug 2003
    Posts
    60

    Thank you

    Thank you nick.cs! I also found that there is no need to update all columns but around 28 of them. But new columns may be added to the update statement and removed. Therefore, I have added another variable to your query which is a flag field to indicate which ones are needed to be updated. Your query also helped me!

    Thanks all!

    This forum is great!

Posting Permissions

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