[ACCEPTED]-MySql Subtract a table from another-mysql
To view all rows in
A except those in
SELECT * FROM A WHERE (field1, field2, ..., fieldN) NOT IN ( SELECT * FROM B ) ;
To 2 actually delete from table
A the rows that 1 are in
DELETE FROM A WHERE (field1, field2, ..., fieldN) IN ( SELECT * FROM B ) ;
I have a very similar requirement to you 4 except that for mine, B is just a subset 3 of A. If you're still looking for an answer:
SELECT * FROM A WHERE NOT EXIST (SELECT * FROM B WHERE A.field1 = B.field1 AND A.field2 = B.field2 etc)
You 2 would need to specify the same condition 1 as doing an inner join on A and B.
Given that you're comparing multiple fields 3 you'll need to either use exists or join. since 2 you're looking to delete its easier yi just 1 use exists.
delete from Tablea Where Exists( Select 1 from tableb where tablea.fielda = tableb.fielda And tablea.fieldb = Tableb.fieldb And...)
You need to create a unique key (it can 3 just be a sequential number)on the original 2 table and the you can select matched or 1 unmatched records ( the 25% or the inverse)
I'd highly recommend making a ID column 3 with auto increment,
but if you can't just do: DELETE FROM a WHERE a.c1 = (SELECT c1 FROM b) AND a.c2 = (SELECT c2 FROM b) AND a.c3 = (SELECT c3 FROM b)
Sorry, just realized 2 it only works for one row...
Well, then the 1 only thing i got is making an ID column, sorry...
More Related questions