UPDATE query to decrease a value



    i am trying to put together an update query to decrease the value of a column in table 1 by the amount contained in table 2.


    table1 is called bootstock the column name is quantity

    table 2 is called work the column name is QTY.


    so far i have this statement


    UPDATE       bootstock
    SET                Quantity = + 1
    WHERE        (Part_number IN
                                 (SELECT        work.Part_number
                                   FROM            work AS bootstock INNER JOIN
                                                             work ON bootstock.Part_number = work.Part_number AND work.bootstock = 'y'))


    this works and it will add the value of 1 to only the specified records in the where statement.

    BUT i need to SET Quantity= the QTY field in the work table.



    bootstock table has a part number 123456 it has a quantity of 4 also it has a part number 654321 and a quantity of 6

    work table has part number 123456 and quantity used is 1 also has part number 654321 and a quantity of 2

    therefore after query has been run

    bootstock table part number 123456 should have a quantity of 3 and 654321 should have a quantity of 4


    cheers all help is appreciated



    Friday, June 27, 2008 12:03 AM

All replies

  • The syntax for update is:

    UPDATE table_name
       [ WITH ( < table_hint > ) ]
       { column_name = { expression | DEFAULT | NULL } } [ ,...n ]
       [ WHERE < search_condition > ]
       [ OPTION ( <query_hint> [ ,...n ] ) ]


    Which clearly means that via T-SQL it is not possible to set one column to another column in another table. However you can set the column to any other column in same table(does not help Sad )


    To make up for this we have to use programming model - Managed Code or Unmanaged(OLEDB).


    Fire two different statements, i.e. fire on first table to get the data, make a parameterized query on the second table to fill out the values.

    Friday, June 27, 2008 5:53 AM
  • you mean to come away from the T-sql and write it in vbcode instead to do the job??




    Saturday, June 28, 2008 11:15 PM