update by quantity

• Steve Collins

  SSCrazy

  Points: 2157

  More actions

  April 1, 2020 at 9:06 pm

  #3739813

  That's intended to work for 1 id from #sale_order at a time.  Is that how you're intending to update the #sale table?

  Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

• michael.cole 47030

  SSC Eights!

  Points: 804

  More actions

  April 14, 2020 at 9:04 am

  #3742642

  Does this do roughly what you are looking for?

  CREATE TABLE #sale
      (
          id INT IDENTITY PRIMARY KEY,
          item_id INT,
          sale_code VARCHAR(36),
          ship_date DATETIME
      );
  
  
  INSERT INTO #sale ( [item_id], [sale_code], [ship_date] )
  VALUES ( 123, 'ABC', '3/1/2020' ); --one item delivered at time of sale
  INSERT INTO #sale ( [item_id], [sale_code], [ship_date] )
  VALUES ( 123, 'ABC', NULL );
  INSERT INTO #sale ( [item_id], [sale_code], [ship_date] )
  VALUES ( 123, 'ABC', NULL );
  INSERT INTO #sale ( [item_id], [sale_code], [ship_date] )
  VALUES ( 123, 'ABC', NULL );
  INSERT INTO #sale ( [item_id], [sale_code], [ship_date] )
  VALUES ( 123, 'ABC', NULL );
  
  INSERT INTO #sale ( [item_id], [sale_code], [ship_date] )
  VALUES ( 456, 'DEF', NULL );
  INSERT INTO #sale ( [item_id], [sale_code], [ship_date] )
  VALUES ( 456, 'DEF', NULL );
  INSERT INTO #sale ( [item_id], [sale_code], [ship_date] )
  VALUES ( 456, 'DEF', NULL );
  
  INSERT INTO #sale ( [item_id], [sale_code], [ship_date] )
  VALUES ( 789, 'DEF', NULL );
  INSERT INTO #sale ( [item_id], [sale_code], [ship_date] )
  VALUES ( 789, 'DEF', NULL );
  INSERT INTO #sale ( [item_id], [sale_code], [ship_date] )
  VALUES ( 789, 'DEF', NULL );
  
  SELECT *
  FROM   #sale;
  
  WITH A
  AS ( SELECT id,
              ROW_NUMBER() OVER ( PARTITION BY item_id,
                                               sale_code
                                  ORDER BY id
                                ) AS RN
       FROM   #sale
       WHERE  ship_date IS NULL )
  UPDATE #sale
  SET    #sale.ship_date = GETDATE()
  FROM   #sale
         INNER JOIN A ON A.id = #sale.id
  WHERE  item_id = 123
         AND sale_code = 'ABC'
         AND RN <= 3;
  
  
  SELECT *
  FROM   #sale;
  
  DROP table #sale