none
Update rows of a table with data from another table including change from each previously updated row RRS feed

  • Question

  • Dear All,
    Please advise how to solve the problem in a best possible way.
    I have 2 tables:

    1. dictionary table tbl1 with 4 columns: number1, number2, qty, date
    sample data:

    nr1001  nr2001  5  14/05/2015
    nr1001  nr2002  3  21/05/2015

    2. user table tbl2 with data to be adjusted and 4 columns as well: id, number1, number2, qty
    sample data:
    1  nr1001  NULL  1
    2  nr1001  NULL  3
    3  nr1001  NULL  2

    Now I need to update each row in tbl2 with data from tbl1 this way:
    - update number2 column with value from column number2 in tbl1 with the earliest date and corresponding values in number 1 column in each table
    - do above only if qty value in tbl1 is >= qty value of the updated row in tbl2
    - the above check in each next row of tbl2 need to include qty value updated in previous rows i.e. tbl1.qty = tbl1.qty - tbl2.qty but data in tbl1 cannot be updated
    - if the qty value in tbl1 (minus sum of qty values in previously updated rows) is < qty value of the row in tbl2 update the row with available qty and create new row in tbl2 with the remaining qty and number2 value of a next row

    The resulting data in tbl2 should look like this:

    1  nr1001  nr2001  1
    2  nr1001  nr2001  3
    3  nr1001  nr2001  1
    4  nr1001  nr2002  1 (new row)


    Any ideas how to do it?

    Thx in advance,


    Dzordz


    Thursday, May 14, 2015 1:03 PM

All replies

  • Your explanation is convoluted and not easy to understand.

    Here's the test data and DDL you SHOULD have provided.

    DECLARE @tbl1 TABLE (number1 VARCHAR(6), number2 VARCHAR(6), qty INT, date DATE)
    INSERT INTO @tbl1 (number1, number2, qty, date) VALUES
    ('nr1001', 'nr2001', 5, '2015-05-14'),
    ('nr1001', 'nr2002', 3, '2015-05-21')
    
    DECLARE @tbl2 TABLE (ID VARCHAR(6), number1 VARCHAR(6), number2 VARCHAR(6), qty INT)
    INSERT INTO @tbl2 (ID, number1, qty) VALUES
    (1, 'nr1001', 1),
    (2, 'nr1001', 3),
    (3, 'nr1001', 2)
    

    "update number2 column with value from column number2 in tbl1 with the earliest date and corresponding values in number 1 column in each table"

    Ok. Easy enough

    SELECT *
      FROM @tbl2 t2
        LEFT OUTER JOIN @tbl1 t1
    	  ON t2.number1 = t1.number1
    	  AND t1.date = (SELECT MIN(DATE) FROM @tbl1 WHERE t1.number1 = number1)

    "do above only if qty value in tbl1 is >= qty value of the updated row in tbl2"

    Add a constaint, so we're only going to update number2 with the value from tbl1 if the qty is also greater than or equal to the quantity in tbl1

    SELECT *
      FROM @tbl2 t2
        LEFT OUTER JOIN @tbl1 t1
    	  ON t2.number1 = t1.number1
    	  AND t1.date = (SELECT MIN(DATE) FROM @tbl1 WHERE t1.number1 = number1)
    	  AND t2.qty >= t1.qty

    "the above check in each next row of tbl2 need to include qty value updated in previous rows i.e. tbl1.qty = tbl1.qty - tbl2.qty but data in tbl1 cannot be updated"

    Well that's not a problem, since none of your test data met the criteria to this point. It's not clear what you're trying to achieve...

    UPDATE cannot add new rows. UPDATE does not function in RBAR fashion, it's a set based operator. It sounds like you may need to issue multiple updates.


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

    Thursday, May 14, 2015 2:37 PM