Need Help with an Update Query 1

[LyndonOHRC]

The following select query pulls the recordset I need for a report. I need the same recordset in the form300 table to be updated and I'm not sure how to go about it...

We are looking for records in the parent table (form300) that have not had a record entered in the child (form100) table. The statement "And form300.obno Is Not NULL" gets that done for me.

<cfquery name="PurgeList" datasource="registry">
Select form300.obno As st_obno, 
 form300.name As st_name, 
 form100.dobn As dobn
From form300 Left Outer Join form100 
 On form300.obno = form100.dobn
Where (dobn is NULL Or Trim(dobn) = ' ') 
 And Trim(form300.name) > '' 
 And Val(form300.obno)<45256 
 And form300.obno Is Not NULL
Order By Val(form300.obno)
</cfquery>

With my limited experience, the only way I know to do my update is by looping through the PurgeList Query and doing the update one row at a time. Too many records for that.

Here is the loop that gets it done, but, it takes about 30 minutes:

<cfoutput query="PurgeList">
 <cfquery name="PutPurged" datasource="registry">
   Update form300
   Set purged=1
   Where obno='#PurgeList.st_obno#'
 </cfquery>
</cfoutput>

I would appreciate help converting the select query into an update query.
Thank in advance

Lyndon

---People Remember about 10% of what you say ---They never forget how you made them feel. Covey

 

[cfStarlight]

Maybe I have this reversed but since you're doing a left join, wouldn't the clause that returned "records in the parent table (form300) that have not had a record entered in the child (form100) table" be more like this:

WHERE form100.dobn IS NULL

The syntax depends on your database, but using the above assumption the general idea is:

--- ms sql psuedo code
UPDATE f3
SET    Column1 = 'A Value' 
FROM   form300 f3 LEFT JOIN form100 f1 ON f3.obno = f1.dobn
WHERE  f1.dobn IS NULL

 

[LyndonOHRC]

I'm sorry, I misspoke.

You are correct. The term "dobn is NULL" is what gives me only childless records.

It worked like a charm.
Thank you!

Lyndon

---People Remember about 10% of what you say ---They never forget how you made them feel. Covey

 

[cfStarlight]

Glad to help