Delete data from two tables and two different columns in second table MySQL

Question

I'm building an endpoint where I can delete a user and all his relationships. The table relationships looks like this:

| id  | followerId | followedId | createdAt           | updatedAt           |
+-----+------------+------------+---------------------+---------------------+
| 188 |         10 |         19 | 2021-02-22 00:00:00 | 2021-02-22 00:00:00 |
| 189 |         10 |         22 | 2021-02-22 00:00:00 | 2021-02-22 00:00:00 |
| 190 |         10 |         23 | 2021-02-22 00:00:00 | 2021-02-22 00:00:00 |
| 197 |         10 |         12 | 2021-03-11 00:00:00 | 2021-03-11 00:00:00 |
| 199 |         19 |         10 | 2021-03-11 00:00:00 | 2021-03-11 00:00:00 |

I've built the following endpoint which worked well, until I got an internal error and realised that if an Id is not in both columns I get internal server error (followerId and followedId):

routes.delete("/profile/:id/edit/delete", (req, res) => {
    let { id } = req.params
    
    db(`
DELETE user
     , follow
     , following
  FROM user
  JOIN relationships as follow
    ON follow.followerId = user.id
  JOIN relationships as following
    ON following.followedId = user.id
 WHERE user.id = ${id}
        `)
        .then((results) => {
            res.send({ message: "user deleted" })
        })
        .catch(err => res.status(500).send(err))
})

Would it be possible to delete the user if the id is only in one column? For example, if I want to delete user with Id 22

` <--- this is a strange thing

Strawberry
– Strawberry

2021-03-15 22:06:54 +00:00
Commented Mar 15, 2021 at 22:06 — Strawberry
– Strawberry, Commented Mar 15, 2021 at 22:06

Yatin Gaikwad · Accepted Answer · 2021-03-15 18:05:56Z

1

If you have setup the relationship table with foreign key constraint and set the DELETE_CASCADE on followerId simple user deletion query will delete all the associated entries in relationship table.

answered Mar 15, 2021 at 18:05

Yatin Gaikwad

1,2102 gold badges14 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Guerreri88 Over a year ago

Thank you @Yatin! I was thinking to do that, but I could not add two FK to the same table. I have altered the table adding one FK to followerId with on delete cascade, and I'm deleting the second one (followedId) with Inner Join and it works now.

Collectives™ on Stack Overflow

Delete data from two tables and two different columns in second table MySQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related