[deletion debacle]

[!--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.

[deletion debacle]

[!--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]

[deletion debacle]

[!--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?

[deletion debacle]

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

[deletion debacle]

[!--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

[deletion debacle]

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!