Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1

    Unanswered: Why is UPDATE statement creating NULL fields?

    I am attempting to update a table that drives a report. The report is at the order level. An order can have several types of demand (revenue $): Ship and/or Backordered. When I update one of these demand fields on the report table, I would expect those orders that have that type of demand to update, and those orders that don't have that type of demand to remain at their current value ($0 in this example which is the table default). But what is happening is that orders that don't have that type of demand are changing to NULL. What am i doing wrong with my update statement that is causing this?

    Code:
    CREATE TABLE #sales(
          OrdNo int,
          StatusGrp varchar(10),
          Demand decimal(10,2)
        )
        INSERT INTO #sales
        VALUES (1,'Ship',100.00)
          ,(1,'BackOrder',20.50)
          ,(2,'Ship',34.55)
          ,(3,'Ship',1244.66)
          
          
        CREATE TABLE #report(
          OrdNo int,
          ShipDmd decimal(10,2) default 0,
          BackorderDmd decimal(10,2) default 0
        )
        
        INSERT INTO #report (ordno)
        SELECT distinct ordno FROM #sales
        
        --update BackorderDmd field
          UPDATE #report
          SET BackorderDmd = (SELECT demand FROM #sales WHERE #report.ordno = #sales.ordno AND statusgrp = 'backorder')
        
        SELECT * FROM #report
    Last edited by clawlan; 10-23-14 at 17:57.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    It's because the results of the inline subquery is NULL. You have no WHERE clause on your update statement, so you are updating *all* rows.

    You should update using joins. Equivalent to your query
    Code:
    UPDATE #report
    SET    BackorderDmd = sales.demand
    FROM   #report As report
     LEFT
      JOIN #sales As sales
        ON sales.ordno = report.ordno
       AND sales.statusgrp = 'backorder'
    ;
    
    (3 row(s) affected)
    Note that this is using an OUTER join, so affects all rows in #report.

    Swap to an INNER join, and you only affect those that have a match available
    Code:
    UPDATE #report
    SET    BackorderDmd = sales.demand
    FROM   #report As report
     INNER
      JOIN #sales As sales
        ON sales.ordno = report.ordno
       AND sales.statusgrp = 'backorder'
    ;
    
    (1 row(s) affected)
    George
    Home | Blog

  3. #3
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1
    ah, that makes sense. For some reason I was thinking that the WHERE clause in the SET statement was enough. Thanks.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    That part is only applying to the "inner" query, not the bit on the "outside" (the UPDATE)
    George
    Home | Blog

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    This might be another solution.
    (If performance was not considered.)

    Code:
    UPDATE #report
     SET   BackorderDmd
         = COALESCE(
              (SELECT demand
                FROM  #sales
                WHERE #sales.ordno = #report.ordno
                 AND  #sales.statusgrp = 'BackOrder')
            , BackorderDmd
           )
    ;

  6. #6
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1
    Quote Originally Posted by tonkuma View Post
    This might be another solution.
    (If performance was not considered.)

    Code:
    UPDATE #report
     SET   BackorderDmd
         = COALESCE(
              (SELECT demand
                FROM  #sales
                WHERE #sales.ordno = #report.ordno
                 AND  #sales.statusgrp = 'BackOrder')
            , BackorderDmd
           )
    ;
    ooo, this is cool. Requires almost no change to my existing code aside from adding the coalesce. thanks.

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Yes, but this will affect every row in the table, even if it doesn't have to.

    You should tailor your queries to be specific.
    George
    Home | Blog

Posting Permissions

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