glyndower

[!--quoteo(post=359546:date=Mar 29 2006, 12:10 AM:name=wickning1)--][div class=\'quotetop\']QUOTE(wickning1 @ Mar 29 2006, 12:10 AM) [snapback]359546[/snapback][/div][div class=\'quotemain\'][!--quotec--] He forgot to bring it outside the quotes. Replace this line: [code]$query = 'DELETE FROM virtualdb_bu2 WHERE mlsnum IN (' . $id_list . ')';[/code] [/quote] Holy Abscence Batman! They are gone! Just like that! Poof! I love you man.... Seriossly, Thank You all.

[!--quoteo(post=359343:date=Mar 28 2006, 11:42 AM:name=shoz)--][div class=\'quotetop\']QUOTE(shoz @ Mar 28 2006, 11:42 AM) [snapback]359343[/snapback][/div][div class=\'quotemain\'][!--quotec--] [code] <?php $query = 'SELECT '               .'v.mlsnum '               .'FROM '               .'virtualdb_bu2 AS v '               .'LEFT JOIN mtable_bu2 AS m '               .'ON v.mlsnum = m.ListID '               .'WHERE '               .'ISNULL(m.ListID) '; $result = mysql_query($query) or die($query."<br />\n".mysql_error()); if (mysql_num_rows($result)) {     $ids = array();     while ($row = mysql_fetch_assoc($result))     {         $ids[] = $row['mlsnum'];     }      $id_list = implode(',', $ids);      $query = 'DELETE FROM virtualdb_bu2 WHERE mlsnum IN ($id_list)';      $result = mysql_query($query) or die($query."<br />\n".mysql_error()); } ?> [/code] [/quote] I would love to report success..but alas I can not Keptain. After considering the above for quite a few seconds MySql reports that [code]"DELETE FROM virtualdb_bu2 WHERE mlsnum IN ($id_list) Unknown column '$id_list' in 'where clause'[/code]

[!--quoteo(post=359123:date=Mar 27 2006, 09:38 PM:name=shoz)--][div class=\'quotetop\']QUOTE(shoz @ Mar 27 2006, 09:38 PM) [snapback]359123[/snapback][/div][div class=\'quotemain\'][!--quotec--] If you're using PHP it may be simpler to do the SELECT for the ids that aren't in mlsnum and then do a [code] DELETE FROM virtuadb_bu2 WHERE v.mlsnum IN etc [/code] [/quote] Can you expand on this thought a bit please?

Yup, but I'm stuck in an older version (MySQL 3.23.58) of MySql which is what is giving me the hives...itch itch

[!--quoteo(post=358799:date=Mar 27 2006, 03:00 AM:name=wickning1)--][div class=\'quotetop\']QUOTE(wickning1 @ Mar 27 2006, 03:00 AM) [snapback]358799[/snapback][/div][div class=\'quotemain\'][!--quotec--] From the documentation: So try this: [code]DELETE FROM virtualdb_bu2 USING virtualdb_bu2 c LEFT JOIN mtable_bu2 w ON c.mlsnum = w.ListID WHERE w.ListID IS NULL [/code] [/quote] Yup, I had already tried something similar, but guess what? Yup, you guessed it no go Houston. SQL-query : DELETE FROM virtualdb_bu2 USING virtualdb_bu2 c LEFT JOIN mtable_bu2 w ON c.mlsnum = w.ListID WHERE w.ListID IS NULL MySQL said: #1064 - You have an error in your SQL syntax near 'USING virtualdb_bu2 c LEFT JOIN mtable_bu2 w ON c.mlsnum = w.ListID WHERE ' at line 1

Im trying to delete data from a table (virtualdb_bu2) that does not have a reference in table (mtable_bu2) in an older version (MySQL 3.23.58) of MySql Ok, so here are the didn't work attempts... DELETE FROM c USING virtualdb_bu2 c LEFT JOIN mtable_bu2 w ON c.mlsnum = w.ListID WHERE w.ListID IS NULL and.... CREATE TEMPORARY TABLE distinct_tours SELECT DISTINCT mtable_bu2.ListID FROM mtable_bu2; ALTER TABLE virtualdb_bu2 ADD linked tinyint default 0; UPDATE virtualdb_bu2 SET linked=1 WHERE virtualdb_bu2.mlsnum=mtable_bu2.ListID; DELETE FROM virtualdb_bu2 where linked=0; ALTER TABLE virtualdb_bu2 DROP COLUMN linked; I'm about to go find a bridge......any help would be appreciated, remember the life you save could be mine!