I'm trying to delete the user from the application, but first I need to delete all the records associated with it from the Chat table. (i.e., to delete a user, I need to delete all messages from the tasks in which it is involved -> Delete all tasks in which it participates -> Delete it from all projects -> And finally delete it from the USERS table).

I make the following query:

select c. *, pt.user_id, pt.project_id from chat c left join projects_tasks pt on c.task_id= pt.id where pt.user_id= 27

He shows me the data that I want to remove from the Chat table.

Now, I want to delete recordings from Chat on the resulting ID

Delete from Chat Where ID In (Select C. *, Pt.user_id, Pt.Project_ID from Chat C Left Join Projects_Tasks PT on C.Task_id= Pt.id WHERE PT.USER_ID= 27)

But an error is issued:

How can I solve this problem?

my database structure:

You need to delete via ID ... "And let's take everything from the table _" SELECT C. *, PT.USER_ID, PT.Project_ID from -where is the logic? Want to delete by id ID and you need to "choose." Even the text of the error prompts -the expression in brackets must contain 1 column (your ID)

InDevX2021-05-19 14:03:40

@INDEVX, thanks for the comment. Then, as I understand it is necessary to do this: Delete From Chat Where ID In (Select C.ID From Chat C Left Join Projects_Tasks PT on C.Task_id= Pt.id Where Pt.user_id= 27) But in this case an error is issued: 1093 -You Can't Specify Target Table 'Chat' for Update In From Clause

lumberspin2021-05-19 14:18:35