Jump to content


Photo

deletion debacle


  • Please log in to reply
12 replies to this topic

#1 glyndower

glyndower
  • New Members
  • Pip
  • Newbie
  • 6 posts

Posted 27 March 2006 - 01:18 AM

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!

#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 27 March 2006 - 04:47 AM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#3 wickning1

wickning1
  • Members
  • PipPipPip
  • Advanced Member
  • 405 posts

Posted 27 March 2006 - 08:00 AM

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:

DELETE FROM virtualdb_bu2 USING virtualdb_bu2 c LEFT JOIN mtable_bu2 w ON c.mlsnum = w.ListID WHERE w.ListID IS NULL


#4 glyndower

glyndower
  • New Members
  • Pip
  • Newbie
  • 6 posts

Posted 28 March 2006 - 12:22 AM

[!--quoteo(post=358799:date=Mar 27 2006, 03:00 AM:name=wickning1)--][div class=\'quotetop\']QUOTE(wickning1 @ Mar 27 2006, 03:00 AM) View Post[/div][div class=\'quotemain\'][!--quotec--]
From the documentation:
So try this:

DELETE FROM virtualdb_bu2 USING virtualdb_bu2 c LEFT JOIN mtable_bu2 w ON c.mlsnum = w.ListID WHERE w.ListID IS NULL
[/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

#5 shoz

shoz
  • Staff Alumni
  • Advanced Member
  • 600 posts

Posted 28 March 2006 - 02:38 AM

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.

Try the following
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)
DROP TABLE virtualdb_bu2
ALTER TABLE virtualdb_bu2_new RENAME virtualdb_bu2
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
DELETE FROM virtuadb_bu2 WHERE v.mlsnum IN etc


#6 glyndower

glyndower
  • New Members
  • Pip
  • Newbie
  • 6 posts

Posted 28 March 2006 - 02:48 AM

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

#7 shoz

shoz
  • Staff Alumni
  • Advanced Member
  • 600 posts

Posted 28 March 2006 - 02:55 AM

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

I noticed that. The solutions are for 3.23.x

#8 shoz

shoz
  • Staff Alumni
  • Advanced Member
  • 600 posts

Posted 28 March 2006 - 03:29 AM

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.

#9 glyndower

glyndower
  • New Members
  • Pip
  • Newbie
  • 6 posts

Posted 28 March 2006 - 04:01 PM

[!--quoteo(post=359123:date=Mar 27 2006, 09:38 PM:name=shoz)--][div class=\'quotetop\']QUOTE(shoz @ Mar 27 2006, 09:38 PM) View Post[/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
DELETE FROM virtuadb_bu2 WHERE v.mlsnum IN etc
[/quote]

Can you expand on this thought a bit please?


#10 shoz

shoz
  • Staff Alumni
  • Advanced Member
  • 600 posts

Posted 28 March 2006 - 04:42 PM

<?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());
}
?>


#11 glyndower

glyndower
  • New Members
  • Pip
  • Newbie
  • 6 posts

Posted 29 March 2006 - 04:32 AM

[!--quoteo(post=359343:date=Mar 28 2006, 11:42 AM:name=shoz)--][div class=\'quotetop\']QUOTE(shoz @ Mar 28 2006, 11:42 AM) View Post[/div][div class=\'quotemain\'][!--quotec--]
<?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());
}
?>
[/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

"DELETE FROM virtualdb_bu2 WHERE mlsnum IN ($id_list)
Unknown column '$id_list' in 'where clause'


#12 wickning1

wickning1
  • Members
  • PipPipPip
  • Advanced Member
  • 405 posts

Posted 29 March 2006 - 05:10 AM

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

$query = 'DELETE FROM virtualdb_bu2 WHERE mlsnum IN (' . $id_list . ')';


#13 glyndower

glyndower
  • New Members
  • Pip
  • Newbie
  • 6 posts

Posted 29 March 2006 - 11:59 AM

[!--quoteo(post=359546:date=Mar 29 2006, 12:10 AM:name=wickning1)--][div class=\'quotetop\']QUOTE(wickning1 @ Mar 29 2006, 12:10 AM) View Post[/div][div class=\'quotemain\'][!--quotec--]
He forgot to bring it outside the quotes. Replace this line:

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


Holy Abscence Batman! They are gone! Just like that! Poof!

I love you man....

Seriossly, Thank You all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users