I want to select data then use update with concat to append some text (Mysql)

Question

I have two schemas arkdb and kalscrosschat. Id is primary key on both.

arkdb has a table called players and fields

Id SteamId            PermissionGroups

1  76561197969383152  Default

2  76561197969383154  Default

Kalscrosschat has a table called discordsteamlinks amd fields

Id  SteamId           DiscordId            InviteCode

1   76561198032704712 150054663054426113   NU123

2   76561198032704714 150054663054426113   ME123

i have a select query.

    Select
        kalscrosschat.discordsteamlinks.DiscordId,
        kalscrosschat.discordsteamlinks.SteamId,
        arkdb.players.SteamId As SteamId1,
        arkdb.players.PermissionGroups
    From
        kalscrosschat.discordsteamlinks Inner Join
        arkdb.players On arkdb.players.SteamId = kalscrosschat.discordsteamlinks.SteamId
    Where
        arkdb.players.PermissionGroups Not Like '%Discord%'

I then need to update the arkdb.players.permissionsgroups to append Discord, to the end.

So 1 76561197969383152 Default would become 1 76561197969383152 Default,Discord

How do i go about this???

Answer 1

You can join the tables in the UPDATE statement like this:

UPDATE arkdb.players p
INNER JOIN kalscrosschat.discordsteamlinks d  on p.SteamId = d.SteamId
SET p.PermissionGroups = CONCAT(p.PermissionGroups, ',', 'Discord')
WHERE p.PermissionGroups NOT LIKE '%Discord%'

or if you have nulls in the column PermissionGroups:

UPDATE arkdb.players p  
INNER JOIN kalscrosschat.discordsteamlinks d on p.SteamId = d.SteamId
SET p.PermissionGroups = CONCAT_WS(',', p.PermissionGroups, 'Discord')
WHERE p.PermissionGroups NOT LIKE '%Discord%' OR p.PermissionGroups IS NULL