[cfaussie] Re: IN versus cfloop and where

[Brett Payne-Rhodes]

Hi Gavin,

I don't think version II will work with an 'AND' anyway. Perhaps an 'OR' 
would work. But I think version I is a better solution regardless. Maybe 
you could test both of them and let us know the definitive answer?

hth,

Brett
B)
Gavin Cooney wrote:
Hi all
 
Anyone know which would be quicker on a huge (couple of million records) 
on mySQL?
 
UPDATE
    table_name
SET
    field1 = <cfqueryparam value="W" cfsqltype="CF_SQL_CHAR">
WHERE
  primary_key of table IN (<cfqueryparam
                                            value="#some_list#"
                                            cfsqltype="CF_SQL_NUMERIC" 
list="Yes">)
 
OR
 
UPDATE
    table_name
SET
    field1 = <cfqueryparam value="W" cfsqltype="CF_SQL_CHAR">
WHERE
    1=1
<cfloop list="#some_list#" index="i">
AND field1 = <cfqueryparam
              value="#i#"
                cfsqltype="CF_SQL_NUMERIC">
</cfloop>
 
Thanks for your help
 
Gavin
---
You are currently subscribed to cfaussie as: [EMAIL PROTECTED]
To unsubscribe send a blank email to 
[EMAIL PROTECTED]

MX Downunder AsiaPac DevCon - http://mxdu.com/


--
Brett Payne-Rhodes
Eaglehawk Computing
t: +61 (0)8 9371-0471
f: +61 (0)8 9371-0470
m: +61 (0)414 371 047
e: [EMAIL PROTECTED]
w: www.ehc.net.au


---
You are currently subscribed to cfaussie as: [EMAIL PROTECTED]
To unsubscribe send a blank email to [EMAIL PROTECTED]
MX Downunder AsiaPac DevCon - http://mxdu.com/