delete problem 1

[QatQat]

Hi everybody,

I have two tables,

table1

ID
name
surname
etc...


table2

ID
name
surname

The second table stores a list od people that have to be removed from the first table

How do I delete all records in table1 that have got same ID of records in table2 ?

I tried using a WHERE....IN(...) statement with no success


Thank you


Qatqat

The reason why my girlfriend can read my thoughts is because mine are properly written! (G.Lepore)

 

[mvrijnsoever]

Hi

The follow section from the MySQL manual describes multi table deleting:

The first multi-table delete format is supported starting from MySQL 4.0.0. The second multi-table delete format is supported starting from MySQL 4.0.2.

The idea is that only matching rows from the tables listed before the FROM or before the USING clause are deleted. The effect is that you can delete rows from many tables at the same time and also have additional tables that are used for searching.

The .* after the table names is there just to be compatible with Access:

DELETE t1,t2 FROM t1,t2,t3 WHERE t1.id=t2.id AND t2.id=t3.id

or

DELETE FROM t1,t2 USING t1,t2,t3 WHERE t1.id=t2.id AND t2.id=t3.id

In the above case we delete matching rows just from tables t1 and t2.


Hope that this answers your question.

Cheers,

Marius

 

[QatQat]

Thanks Marius but that was the first thing I tried that; I think that it is not supported yet

DELETE FROM.....USING....
returns an error and I am using vers 4 as they mention there.

so does the other one



Any other idea?


Thank you.


Qatqat

The reason why my girlfriend can read my thoughts is because mine are properly written! (G.Lepore)

 

[QatQat]

I found out that

DELETE table1, table2 from table1 t1, table2 t2 WHERE t1.ID = t2.ID

works correctly but it deletes all related records from the two tables.

I want to be able to delete from table 1 people listed in table 2 without emptying table 2.

Is there a way?

DELETE from Table 1 t1 USING table 2 t2 where t1.ID = t2.ID sounds perfect for me but it does not work.


Bye

Qatqat


The reason why my girlfriend can read my thoughts is because mine are properly written! (G.Lepore)

 

[mvrijnsoever]

Altough this solution is not ideal, it is a quick fix. Try a "Select INTO TempTable" followed by the "Delete From Where" statement. By first copying your data into a temp table and then performing the delete action, only data from one of your tables is deleted.

Probably not the best way to do it, but a quick fix.

Cheers,

Marius

 

[QatQat]

Thanks for your time. That would work

I have sorted it out differently.
Due to other funcitons needed and procedures, I imported the DB in MS SQL and work on it now. I am not too happy about, but sometimes MySQL shows its limits.

I am however very surprised that functions described in the MySQL manual actually don't work.


A star for your time


Bye

Qatqat




The reason why my girlfriend can read my thoughts is because mine are properly written! (G.Lepore)