Skip to main content
Known Participant
January 28, 2007
Question

Updating Qty

  • January 28, 2007
  • 1 reply
  • 232 views
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#'
</cfquery>

<cfloop query="SEL">

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

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

<cfquery name="Update1" datasource="#application.ds#">
UPDATE products_table
SET Qty = #newqty#
WHERE Part_No = #SEL.HistorySouthPartNo#
</cfquery>
</cfloop>
This topic has been closed for replies.

1 reply

Inspiring
January 28, 2007
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.