syntax help please?

[design@glyndowe]

///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 i, distinct_tours dt SET i.linked=1
///WHERE i.mnum=dt.listID;
///DELETE FROM virtualdb_bu2 where linked=0;
///ALTER TABLE virtualdb_bu2 DROP COLUMN linked;

I am trying to delete rows from one table (virtualdb_bu2) that do not have corresponding rows in another table (mtable)

The join is virtualdb_bu2.mnum = mtable_bu2.ListID.

I have the above code which seems to break somewhere around UPDATE virtualdb_bu2....

Any help would be appreciated.

TIA

[chazzy]

you can't update 2 tables at once, nor do i understand why you want to try to do that.

[design@glyndowe]

I didn't think I was....I'm simply trying to delte stuff in one table that is not reffered to another table. I also was muckin about with: ///DELETE FROM virtualdb_bu2 WHERE mnum NOT IN (SELECT ListID FROM mtable_bu2); but that didn't work out either... I'm really kinda frustrated as I must be overthinking this cuase it simply can't be this difficult.

[chazzy]

this is where you're (trying to) updat(ing) multiple tables

UPDATE virtualdb_bu2 i, distinct_tours dt SET i.linked=1

[design@glyndowe]

So instead of: ///UPDATE virtualdb_bu2 i, distinct_tours dt SET i.linked=1

I should do: UPDATE virtualdb_bu2 i SET i.linked=1

??

[chazzy]

you don't need to (and typically shouldn't) use table aliases in an update, but yes.

[design@glyndowe]

argh...ok here is what I came up with:
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;

of course it dosn't work either but..... it LOOKS good!

MySQL said:


#1109 - Unknown table 'mtable_bu2' in where clause

[design@glyndowe]

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

also did not work...syntax error...

[Nedals]

I am trying to delete rows from one table (virtualdb_bu2) that do not have corresponding rows in another table (mtable)

If that's what you want to do, then why are you using CREATE and ALTER. Those are used to create a new table and to alter the structure of a table respectively.

Do you have to do it in one SQL instruction?
I'm not sure what language you are using, but something like this could work for you

PSEUDO CODE:

SELECT DISTINCT mlsnum FROM virtualdb_bu2
(use DISTINCT here so you only get one of each occurance.
 Otherwise it will try to delect non-existant items that will cause an error) 
while (fetch loop) {
  found = ...SELECT COUNT(*) FROM mtable_bu2 WHERE ListID=<fetched item>
  if (!found) {
    DELETE FROM virtualdb_bu2 WHERE mlsnum = <fetched item>
  }
}