Database Query - using Update 1

[iao]

I am having trouble with the UPDATE SQL query. What I am trying to do is to update descriptions in table A (which is the display table) and make it the same as the descriptions in table B (which is the database table).

So, I created this query to give me the results of which descriptions are different:

<CFQUERY NAME=&quot;Test1&quot; DATASOURCE=&quot;etcetra&quot; DBTYPE=&quot;Oracle80&quot;>
SELECT FN.TC_WORK_ORDER.Description
FROM FN.TC_WORK_ORDER, FN.TC_WO_DSP
WHERE FN.TC_WO_DSP.WO = FN.TC_WORK_ORDER.WO
AND FN.TC_WO_DSP.Description <> FN.TC_WORK_ORDER.Description
</CFQUERY>

This simply give me all the descriptions from the database table which are different (using #Test1.Description# in CFOUTPUT tags). However, what I want to do is actually update the display table (again, table A) when the database table (table b) is changed.

Whenever I try a Update query, it works fine if only one description has changed. If more than one changes, I get an error.

Here is my second query. Any ideas on how to fix this so that it will accomodate mulitple changes, instead of only one? Any help is much appreciated. Thanks!

<!--- <CFQUERY NAME=&quot;Test2&quot; DATASOURCE=&quot;etcetra&quot; DBTYPE=&quot;Oracle80&quot;>
UPDATE FN.TC_WO_DSP
SET FN.TC_WO_DSP.DESCRIPTION = FN.TC_WORK_ORDER.Description
WHERE FN.TC_WO_DSP.Description = '#Test1.Description#'
</CFQUERY> --->

 

[GunJack]

Hey ecojohnson,

When you output #test.description#, you are basically accessing one record in the &quot;test&quot; recordset. I believe it is the first record in the recordset so it will work fine when there is only one record. For multiple records, you want to look through the recordset and update each record. I believe this will do what you want although an SQL expert might be know a way to do this just in SQL.

<CFQUERY NAME=&quot;Test1&quot; DATASOURCE=&quot;etcetra&quot; DBTYPE=&quot;Oracle80&quot;>
SELECT FN.TC_WORK_ORDER.Description,
FN.TC_WORK_ORDER.WO

FROM FN.TC_WORK_ORDER, FN.TC_WO_DSP
WHERE FN.TC_WO_DSP.WO = FN.TC_WORK_ORDER.WO

AND FN.TC_WO_DSP.Description <>
FN.TC_WORK_ORDER.Description
</CFQUERY>

<cfloop query=&quot;test&quot;>
<CFQUERY NAME=&quot;UPDATE&quot; DATASOURCE=&quot;etcetra&quot; DBTYPE=&quot;Oracle80&quot;>
UPDATE FN.TC_WO_DSP
SET FN.TC_WO_DSP.DESCRIPTION = '#Test1.Description#'
WHERE FN.TC_WO_DSP.Wo = #test1.Wo#
</CFQUERY>

</cfloop>

This assumes that the &quot;WO&quot; field is a primary key and that it is numeric. If it's a text field, you'll need to add single quotes around it like this:

WHERE FN.TC_WO_DSP.Wo = '#test1.Wo#'

Hope this helps,
GJ