Add value of column to SQL CONCAT query

Question

I am trying to add the value of a column into a SQL CONCAT query to be able to update another column, let me demonstrate.

I need the value of the column "winner" and add it to my query, since what I want to accomplish is for the query to say SELECT team2_name FROM x WHERE matchid = 1.

This is what I've tried so far.

UPDATE tournament_matches 
SET match_id = 5 
WHERE team_name IN (
    SELECT CONCAT(@winner, '_name') 
    FROM get5_stats_matches 
    WHERE match_id = 1
);

But it doesn't affect any row since CONCAT(@winner, '_name') doesn't display team2_name which I need it to do.

Where do I go wrong?

Why do you have @ before winner? That's using a user-defined variable, not the table column. — Barmar
– Barmar, Commented Mar 5, 2021 at 15:45
If I just type winner + '_name' it will update 2 rows instead of just one. — Emrik Ahlström
– Emrik Ahlström, Commented Mar 5, 2021 at 15:51
Please post sample data from both tables and the desired result. — Barmar
– Barmar, Commented Mar 5, 2021 at 15:52
It should be CONCAT(winner, '_name'), not winner + '_name' — Barmar
– Barmar, Commented Mar 5, 2021 at 15:53

Barmar · Accepted Answer · 2021-03-05 17:00:50Z

1

I think this is what you want:

UPDATE tournament_matches
SET match_id = 5
WHERE team_name IN (
    SELECT IF(winner = 'team1', team1_name, team2_name)
    FROM get5_stats_matches
    WHERE match_id = 1);

You can't use an expression to specify a column name directly.

Demos:

Winner = Team1

Winner = Team2

edited Mar 5, 2021 at 17:00

answered Mar 5, 2021 at 15:59

Barmar

789k57 gold badges555 silver badges669 bronze badges

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

11 Comments

Emrik Ahlström Over a year ago

This seems to be working quiet good, however. It updates 2 rows when the SELECT IF statement goes to false, why is that?

Barmar Over a year ago

There must be two rows with team_name = 'Svertila'. Which one should it update if it doesn't update both?

Emrik Ahlström Over a year ago

It's really just one row with team_name = 'Svartlila', I tried the SELECT IF(winner = 'team1', team1_name, team2_name) and it worked fine and gave back the correct results, when I add it to the whole query, it only works if team1 is the winner, if we change the column value so that team2 is the winner, 2 rows updates.

Barmar Over a year ago

What is the team name of the 2nd row that's being updated?

Barmar Over a year ago

I asked you to provide sample data, I'll ask again.

|

Collectives™ on Stack Overflow

Add value of column to SQL CONCAT query

1 Answer 1

11 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

11 Comments

Your Answer

Sign up or log in

Post as a guest

Related