going crazy over delete

cardfan

The following works locally...

DELETE demomile FROM demo, demomile WHERE demo.siteid = '37' AND demomile.demoid = demo.id;

...but get the following error on uploaded site

You have an error in your SQL syntax near 'demomile FROM demo, demomile WHERE demo.siteid = '37' AND demomile.demoid = ' at line 1

What am I missing? ...driving me crazy for the last few days

Thanks for any help

MarkR

That is not a standard SQL delete syntax.

Are you completely sure that your development and production servers use the same version of every component (MySQL, especially)?

If not, you will run into trouble.

Mark

Piranha

I think that I am pretty good with SQL, but I can't see from what table the delete will be made. May I suggest that you use a standard SQL delete and a subquery with select to get the ids that you want to delete.

cardfan

Thanks for replying MarkR!

...using mysql 4.0.24-nt locally and 3.23.58 on remote. Not seeing any difference in syntax on dev.mysql.com. After re-skimming through site only problem I might run into is the deletion of rows from multiple tables(not using thus far), so still stuck on why the current statement doesn't work ...Any other ideas on what the problem might be?

cardfan

Thanks to you to Piranha ...sorry been awhile on sql so picking it back up. Current DB is pretty simple and straight forward

The delete statement is trying to delete from table demomile all records that have a matching foreign key to table demo ...hope I explained that correctly

Thanks

Roger_Ramjet

Multi-table delete only begins with 4.0 so that is your problem, production server is 3.23 so not supported.

cardfan

Agree... Is referencing multiple tables the same? only deleting from one table demomile, but referencing table demo for a key match. Apologize if this is a stupid question... must not be understanding the manual

Roger_Ramjet

No, cannot use joined table ref before 4.0.

3.anything is too old to still be using, upgrade the production server.

To make this work in 3.32 you have to select all the demoid in one query then use them in the delete query

cardfan

Just FYI - I have requested provider to update our mysql version

So as not to piss anyone off, just asking questions to understand underlying problem and or my misunderstanding. Manual quotes similar delete statements as what I posted but suggestions posted keep referring to problems in versions, I do lean towards that as a solution(lord knows everything I've tried hasn't worked) just don't understand what I'm missing in the manual...

Roger_Ramjet

From MySQL 4.0, you can specify multiple tables in the DELETE statement to delete rows from one or more tables depending on a particular condition in multiple tables.

😉

cardfan

Missed that ...again thanks to all, sure everyone understands the newbie frustration and the relief when others take the time and effort to help!!! Really do appreciate it!!!

Only hope down the line I can do the same for someone else ...🙂 Will update progress when update is made and all is well.

Piranha

You really should get the database version upgraded, it will make your life much easier. I suggest that you get it upgraded to the latest stable version, 5.0.37 at the moment. If your provider refuses to upgrade I suggest that you change provider, they should make life easier for the paying customer (you), you have no obligation at all to them.

By the way, there are such a thing as a stupid question. But your question was not stupid at all. You defenetley do the right thing, something is not working, you have tried to check why and then come here and ask if someone else can explain it. And you read the answers you get and act upon them.

Roger_Ramjet

I can recommend servage.net for anything but the busiest site. Amazing range of options and services at a really good price. PHP 5 and databases with choice of mysql 4 or 5 plus easy autoinstall of all the most common apps like Wordpress, OSCommerce, ZenCart, PHPFusion, Postnuke, etc, etc. And pretty good support to boot.
Well worth a look.

I will say that when I hit it with Webcam.com and its 168 concurrent requests the site got a little overstressed - still worked but some of the browser shots were missing images.

Sxooter

Keep in mind that 3.23 AND 4.0 have both been end of lifed, i.e. they are no longer actively developed, and anything other than security bugs will likely not ever get fixed.

4.1 is the old version of mysql still under active development.

5.0 is considered the main line stable version.

5.1 is due out of beta "any day now" (could be a day to a year from past experience) hehe You could start development now with it for something that might not be deployed for a year or so.

5.2 is in alpha and is only suitable for personaly use / testing on data you can replace if it gets eaten.

Doing active development for 3.23 and 4.0 makes no sense, given that they are basically dead code branches.