0

Background:

I have three tables that work together:

  • tg_users (primary key is 'userid')
  • tg_type1_user (has tg_users.userid as a foreign key and column named 'name')
  • tg_type2_user (has tg_users.userid as a foreign key and column named 'name')

A given userid may be associated with one or both user types.

Problem:

I would like to remove any entry from tg_users for which the userid is not associated with either user type. (Ultimately, this will be executed from PHP using mysqli.)

My (non-working) solution:

Create a view named tg_dead_userids with the following definition:

select u.userid, a.name as a_name, b.name as b_name
from tg_users u
left join tg_type1_user a on a.userid=u.userid
left join tg_type2_user b on b.userid=u.userid
;

Perform the cleanup using:

delete from tg_users 
where userid in (
  select userid 
  from tg_dead_userids 
  where a_name is null
  and   b_name is null
);

... but mysql will not allow me to perform this delete because tg_dead_userids is referencing tg_users.

Suggestions?

I realize that I can perform a query on the tg_dead_userids to find all entries with null a_name and b_name, fetch the resulting list of userids, and loop over the userids to issue a delete statement for each (or create a single delete statement using an 'in' clause).

I was hoping, however, to perform this with a single mysqli transaction.

Any thoughts/suggestions? Or will I need to go with multiple transactions?

Thanks much for any/all help.

MikeMayer67
  • 570
  • 6
  • 17
  • 1
    See https://stackoverflow.com/a/12969601/5389997 and https://stackoverflow.com/a/4471359/5389997 answers from the duplicate question. – Shadow Sep 08 '20 at 14:05
  • Thank you. When I did my search of SO prior to submitting the question, I explicitly included 'view' in every search I tried. I failed to try searching on 'subquery.' – MikeMayer67 Sep 08 '20 at 14:19
  • @Shadow: If you make your comment an answer, I will mark it as the solution. – MikeMayer67 Sep 08 '20 at 14:20
  • The point of duplicate links is that we do not keep answering the same question over and over agsin! If the duplicate helped you, pls consider upvoting the question and the answer that helped you! – Shadow Sep 08 '20 at 20:14

0 Answers0