latest version (is that 4.23). Anyway, downloaded it today. I've also tried the value list solution and also looping around the results from the first query to delete one record at a time. All to no avail.
This is crazy. The logic is right but it is just not working out here. -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Steve Onnis Sent: Wednesday, 9 July 2003 4:53 PM To: CFAussie Mailing List Subject: [cfaussie] Re: Strange behaviour with MYSQL What version of mySQL are you running? You could try doing 2 queries <cfquery datasource="" name="getList"> SELECT sav.id FROM standard_attribute_value sav WHERE sav.id = savt.standard_attribute_value_id_fk AND sav.standard_attribute_id_fk = #Val(this.id)# </cfquery> <cfquery> DELETE FROM standard_attribute_value_translation savt WHERE IN (#ValueList(getList.ID)#) </cfquery> Steve -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Jordan Thomas Sent: Thursday, 10 July 2003 12:41 AM To: CFAussie Mailing List Subject: [cfaussie] Re: Strange behaviour with MYSQL Yes, I've tried without queryparam to no avail. Thanks Steve, good tip. The query param tag is used not to only validate values though. It also creates a prepared statement that the database can use to save it from having to build a query plan each time a query changes and is then exectued. for insance: select somerow from table where id = 5 is clearly different from select somerow from table where id = 6 but if we use cfqueryparam it creates a query that looks like this select somerow from table where id = ? the '?' is variable that allows the query to always have the same text but at the same time allows the id value to change. Normally, as soon as the text in a query changes, that is the moment the database needs to recompile it and build a new query plan which obviously wastes time. If you already knew just ignore this otherwise I hope this helps. cheers Jordan -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Steve Onnis Sent: Wednesday, 9 July 2003 4:18 PM To: CFAussie Mailing List Subject: [cfaussie] Re: Strange behaviour with MYSQL You dont HAVE to use CFQUERYPARAM in your queries. If your trying to validate int values, you could just as easily use VAL() to do it DELETE FROM standard_attribute_value_translation savt WHERE EXISTS ( SELECT sav.id FROM standard_attribute_value sav WHERE sav.id = savt.standard_attribute_value_id_fk AND sav.standard_attribute_id_fk = #Val(this.id)# ); If anyone tries to pass text into the function its just going to evaluate to 0 anyway Steve -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Toby Tremayne Sent: Thursday, 10 July 2003 12:15 AM To: CFAussie Mailing List Subject: [cfaussie] Re: Strange behaviour with MYSQL -----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 you sure it's not just the queryparam causing your problems? I've had some weird issues with queryparam and mysql in the past - one's I've never been able to come up with a satisfactory answer for - perhaps try the query as is but use the raw variable instead of the queryparam? Toby On Wednesday 09 July 2003 23:40, you wrote: > Thanks Tim, > > It is strange though that this query works in the Mysql client but no > through CF. I actually want to delete from multiple tables but I just cut > the query down a bit to simplify it so I would prefer to use this syntax > for now. Super frustrating! Is mysql just not up to scratch? I have been > using it now for the last year and a bit with no problems....unitl now.... > > >:O( > > -----Original Message----- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] Behalf Of Tim > Sent: Wednesday, 9 July 2003 3:06 PM > To: CFAussie Mailing List > Subject: [cfaussie] Re: Strange behaviour with MYSQL > > > How about: > > DELETE > FROM standard_attribute_value_translation savt > WHERE EXISTS ( SELECT sav.id > FROM standard_attribute_value sav > WHERE sav.id = savt.standard_attribute_value_id_fk > AND sav.standard_attribute_id_fk = <cfqueryparam value="#this.id#" > cfsqltype="CF_SQL_INTEGER"> ); > > - timmah > > Jordan Thomas spoke the following wise words on 9/07/2003 8:09 PM EST: > >When I try to run the following <cfquery> nothing happens. It throws no > >error but the data is still there. > > > ><cfset this.id = 5> > > > ><cfquery name="qDeleteTranslations" datasource="scoop"> > > DELETE standard_attribute_value_translation > > FROM standard_attribute_value_translation savt, > > standard_attribute_value > > >sav > > WHERE savt.standard_attribute_value_id_fk = sav.id > > AND sav.standard_attribute_id_fk = <cfqueryparam value="#this.id#" > >cfsqltype="CF_SQL_INTEGER" maxlength="8"> > ></cfquery> > > --- > 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/ > > > --- > 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/ - -- - -------------------------------- Life is poetry - write it in your own words - -------------------------------- Toby Tremayne Code Poet and Zen Master of the Heavy Sleep Senior Technical Consultant Lyricist Software www.lyricist.com.au +61 416 048 090 ICQ: 13107913 -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.2-rc1-SuSE (GNU/Linux) iD8DBQE/DCNK6KYxVcB16DkRAocHAJwLtQTGTIUQJwnPGfVQ81oi+0B1/gCfcWFw 5mZ+dBnFLmM1yFYQ/Bq0lJg= =JqI3 -----END PGP SIGNATURE----- --- 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/ --- 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/ --- 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/ --- 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/ --- 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/
