CODEWITHSUNDEEP
mysqlsql
Ricky
Ricky

Reputation: 181

How do I update a table that is being queried within the query itself?

I'm trying to update a table through a query, the query itself is created using the results of queries to other tables (one of which is the table we are updating)...

However, I keep getting an error when I try to execute the query... After some research I found out I have to encase the inner query with a SELECT * FROM ()... but this doesn't seem to have worked...

I can't figure out how to bypass this error with my MySQL Query...

This is the error I'm getting...

[Err] 1093 - You can't specify target table 'players' for update in FROM clause

This is my query...

DELETE FROM players WHERE name='Henry' AND player_group_id IN
(
    SELECT id FROM playergroups WHERE player_set_id=
    (
        SELECT id FROM playersets WHERE player_name=
        (
            SELECT name FROM
            (
                SELECT name FROM players WHERE player_group_id=
                (
                    SELECT id FROM playergroups WHERE player_set_id=
                    (
                            SELECT id FROM playersets WHERE player_name='Henry'
                    )
                )
            ) AS P1
        )
    )
);

Upvotes: 0

Views: 124

Answers (2)

Bob D.
Bob D.

Reputation: 1

You should really find a better way to navigate though the tables, but where exists is a good way to around that error. Or you could stage off the data and delete based of PIs.

DELETE FROM players A
WHERE name = 'Henry'
AND WHERE EXISTS 
(
SELECT 1 
FROM playersgroup B 
WHERE A.players_group_id = B.players_group_id
)

ETC.. ETC.. Depending on how many tables you need to reference.

Upvotes: 0

Azlina T
Azlina T

Reputation: 176

use joins, avoid to much use of nested statements

Upvotes: 1

Related Questions