Jump to content

deletion debacle


glyndower

Recommended Posts

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!
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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]
Link to comment
Share on other sites

[!--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
Link to comment
Share on other sites

[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]
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

[!--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?
Link to comment
Share on other sites

[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]
Link to comment
Share on other sites

[!--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]
Link to comment
Share on other sites

[!--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.
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.