CODEWITHSUNDEEP
mysqlsql
Emrik Ahlström
Emrik Ahlström

Reputation: 105

Add value of column to SQL CONCAT query

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.

enter image description here

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?

Upvotes: 0

Views: 70

Answers (1)

Barmar
Barmar

Reputation: 780798

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

Upvotes: 1

Related Questions