deletion debacle

[glyndower]

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!

[fenway]

Well, you should be able to use a INSERT INTO ... SELECT WHERE... and use you first LEFT JOIN example to create a temporary table, and then DELETE the rows that are found there, no? Or is there no multi-table support at all? I don't recall.

[wickning1]

From the documentation:

[!--quoteo--][div class=\'quotetop\']QUOTE[/div][div class=\'quotemain\'][!--quotec--] Note: The syntax for multiple-table DELETE statements that use table aliases changed between MySQL 4.0 and 4.1. In MySQL 4.0, you should use the true table name to refer to any table from which rows should be deleted:

DELETE test FROM test AS t1, test2 WHERE ...

In MySQL 4.1, you must use the alias:

DELETE t1 FROM test AS t1, test2 WHERE ...[/quote]
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]

[glyndower]

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

[shoz]

[quote]
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.
[/quote]
Try the following
[code]
CREATE TABLE
virtualdb_bu2_new
    SELECT
    v.*
    FROM
    virtualdb_bu2 AS v
    LEFT JOIN
    mtable_bu2 AS m
    ON
    v.mlsnum = m.ListID
    WHERE
    !ISNULL(m.ListID)
[/code]
[code]
DROP TABLE virtualdb_bu2
[/code]
[code]
ALTER TABLE virtualdb_bu2_new RENAME virtualdb_bu2
[/code]
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]

[glyndower]

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

[shoz]

[quote]
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
[/quote]
I noticed that. The solutions are for 3.23.x

[shoz]

I should also mention that you may need to add an "ALTER TABLE" to recreate indexes and an auto_increment column if you have one on the table.

To avoid that, you could remove all rows from the original table instead of dropping it and then do "INSERT INTO SELECT FROM tmp table". You'd make the tmp_table a TEMPORARY TABLE in this case.

[glyndower]

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

[shoz]

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

[glyndower]

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

[wickning1]

He forgot to bring it outside the quotes. Replace this line:

[code]$query = 'DELETE FROM virtualdb_bu2 WHERE mlsnum IN (' . $id_list . ')';[/code]

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