How do I update one column value to contain all of the value in two columns of a table in MySQL?-CodePudding

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?

CodePudding user response：

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;