I am a college student currently studying SQL attack and prevention. There is an exercise where we need to
Update your first_name to be the email and password of all users who is
an admin (assume that there is a field in the users table called
is_admin where it’s 0 if the user is not an admin, or 1 if the user is
an admin).
This way, when you log out and log back in, instead of saying Welcome
[your first_name], it would say Welcome [whatever was stored in
first_name field].
Lets assume that there is 6 row in my users table and that my id is 6
I tried to use group concat for email and password
SELECT group_concat(email, " ", password)
AS account_information FROM users
WHERE is_admin = 1
So far it works, it returned 1 row with all of the email and password of users who are an admin and I thought that this is the code I should subquery to be set for my first_name. And now I subqueried it to update my first_name with this code.
UPDATE users
SET first_name = (SELECT group_concat(email, " ", password) AS account_information
FROM users
WHERE is_admin = 1)
WHERE id = 6
I got an error 1093: saying that I can’t specify target table ‘users’ for UPDATE in FROM clause
Can someone help me with this exercise?
>Solution :
Use a CROSS join of the table to a query that returns the concatenated values:
UPDATE users u
CROSS JOIN (SELECT GROUP_CONCAT(email, ' ', password) new_name FROM users WHERE is_admin = 1) t
SET u.first_name = t.new_name
WHERE u.id = 6;