I am using this code/query to delete bogus users using a list from 'bogus' table and Obviously this query is not correct and shows error: Unknown column 'bogus.user' in 'where clause' Consider that tables sample and bogus have ONLY ONE COLUMN each and I want to delete rows from sample table only retaining the data of table bogus.
delete from sample where sample.user=bogus.user;
How about:
delete from sample where sample.user in (SELECT user FROM bogus);
I think that's the savest way. It's probably possible to put both tables in a single statment without a join or nested select. But If you do that wrong you risk deleting both tables content. Thus I'd say it's better to do it this way.
You need to join for this
delete s from sample s
join bogus b on b.user = s.user
delete from sample where user in (select user from bogus)