1 Reply Latest reply on Jan 28, 2007 6:15 AM by Dan Bracuk

    Updating Qty

    RyanG24 Level 1
      Hi i need to update a table with qtys from another table, i have tried the query below but its not updating

      any ideas what i should do?

      <cfquery name="SEL" datasource="#application.ds#">
      SELECT *
      FROM supplierhistory SH
      WHERE SH.HistoryQuoteNo = '#session.order1#'

      <cfloop query="SEL">

      <cfquery name="QQE" datasource="#application.ds#">
      SELECT *
      FROM products_table
      WHERE PartNo = '#SEL.HistorySouthPartNo#'

      <cfset newqty = QQE.Qty + SEL.HistoryQty>

      <cfquery name="Update1" datasource="#application.ds#">
      UPDATE products_table
      SET Qty = #newqty#
      WHERE Part_No = #SEL.HistorySouthPartNo#
        • 1. Re: Updating Qty
          Dan Bracuk Level 5
          You don't need loops. You can do it all with one query. It starts with

          update pt
          set qty = qty + historyQty
          from product_table pt join supplierHistory on part_no = HistorySouthPartNo
          where HistoryQuoteNo = '#session.order1#'

          Depending on what db you are using, you might have to experiment with the syntax a bit. My example works in redbrick.