'Is there a way to replace all values that appear on another table in SQL?
I need to make a query to get all records whose score is 70 or more and replace all nicknames with their corresponding proper name. I'm using MySQL
Table A
| name | score |
|---|---|
| Nick | 75 |
| Kate | 76 |
| Robert | 49 |
| John | 91 |
| Jenny | 87 |
| Bill | 29 |
| Gabrielle | 57 |
| Taylor | 88 |
| Joseph | 68 |
The desired output should be the following:
Query Result
| name | score |
|---|---|
| Nicholas | 75 |
| Kate | 76 |
| John | 91 |
| Jennifer | 87 |
| Taylor | 88 |
Notice that the names Nick and Jenny were replaced.
Currently, the way I'm doing it is using REPLACE but that doesn't scale very well so I was wondering if there's a way to get the nicknames from Table B and use it on my query. My current query looks like this:
SELECT
-- repeat this for every nickname
REPLACE(REPLACE(name,'Nick','Nicholas'),'Jenny','Jennifer') AS name,
score
FROM
table_a
WHERE
score >= 70;
Table B
| nickname | proper_name |
|---|---|
| Nick | Nicholas |
| Jenny | Jennifer |
| Bill | William |
| Gaby | Gabrielle |
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
