Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2003
    Posts
    233

    Unanswered: When then muliple colum update 1 statement

    I have about 5 statements like the update below, depending on the PID different columns will be update "C2005, G2005,E2005...."

    I would like to use 1 update statement in stead of 5 to update all columns below are 2 original update statements and my attempt at when then update. Note a different column is updated depending on the PID.

    If when then isnt possible, any other suggestions are welcomed. Thanks
    UPDATE #Sec
    SET C2005 = Pos.USD / 1000
    FROM #Sec INNER JOIN
    Pos ON #Sec.ID = Pos.ID
    WHERE (Pos.PID = 'B')

    UPDATE #Sec
    SET G2005 = Pos.USD / 1000
    FROM #Sec INNER JOIN
    Pos ON #Sec.ID = Pos.ID
    WHERE (Pos.PID = 'G')

    UPDATE #Sec
    WHEN (Pos.PID = 'C') THEN SET C2005 = Pos.USD / 1000 end
    WHEN (Pos.PID = 'G') THEN SET G2005 = Pos.USD / 1000 end
    WHEN (Pos.PID = 'E') THEN SET E2005 = Pos.USD / 1000 end
    FROM #Sec INNER JOIN
    Pos ON #Sec.ID = Pos.ID

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Code:
     UPDATE    #Sec
    SET C2005 = CASE WHEN Pos.PID = 'C'  THEN Pos.USD / 1000 ELSE C2005 end
    , G2005 = CASE WHEN Pos.PID = 'G'  THEN Pos.USD / 1000 ELSE G2005 end
    FROM       #Sec INNER JOIN
    			Pos ON #Sec.ID = Pos.ID
    WHERE Pos.PID IN('C', 'G')
    Just FYI - this looks like a pretty poor design to me.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Oct 2003
    Posts
    233
    Quote Originally Posted by Pootle Flump
    Code:
     UPDATE    #Sec
    SET C2005 = CASE WHEN Pos.PID = 'C'  THEN Pos.USD / 1000 ELSE C2005 end
    , G2005 = CASE WHEN Pos.PID = 'G'  THEN Pos.USD / 1000 ELSE G2005 end
    FROM       #Sec INNER JOIN
    			Pos ON #Sec.ID = Pos.ID
    WHERE Pos.PID IN('C', 'G')
    Just FYI - this looks like a pretty poor design to me.

    thanks for the post, What about it is poor design, can u suggest better design? I just started sql server last week and trying to learn best way of doin

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    One issue is that this looks like it is producing (or on the way to producing) a crosstab. Crosstabs are best produced in front end applications, not SQL Server.

    If, however, you decide that SQL Server is the best thing for this then typically you would produce this sort of information at runtime, not store the data. This is because of normlisation principles. Splendid article:
    http://www.tonymarston.net/php-mysql...se-design.html
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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