How to replace NULL with value in MySQL?

Question

I have a table with soccer scores. Whenever I add a score to one player it automatically adds NULL in the same row for the other players:

Like this:

player1 1-0, NULL, 4-1
player2 5-2, 4-1, NULL
player3 2-1, NULL, NULL

What I want is when I add a new score to a player, the Null should be replaced with that value (for example for player one it should be 1-0, 4-1 and not 1-0, NULL, 4-1)

How can I do that?

My query is the INSERT into databasename (column) Value (Valuex); — 10now
– 10now, Commented Jan 25, 2013 at 15:29
what are your columns names? How do you represent the opponent the players in the first column? — Leo supports Monica Cellio
– Leo supports Monica Cellio, Commented Jan 25, 2013 at 15:39
If you want meaningful help, please post your table definition and the exact SQL query you're running. — Neville Kuyt
– Neville Kuyt, Commented Jan 25, 2013 at 16:01

peixe · Accepted Answer · 2013-01-25 15:27:24Z

3

Even if its easy to find on the manual, something like this should work...

UPDATE yourTable p
SET p.SomeField = p.NewValue
WHERE p.SomeField is NULL;

edited Jan 25, 2013 at 15:27

answered Jan 25, 2013 at 15:19

peixe

1,3023 gold badges14 silver badges31 bronze badges

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

2 Comments

10now Over a year ago

Is there something like this: "replace null if there is one, if not add the value in a new row"? This replaces only the first null and not all nulls right?

peixe Over a year ago

This will just replace all the NULLs with the value you specify on p.NewValue which may be a string, as well...

Adrian Smith · Accepted Answer · 2013-01-25 15:32:20Z

0

In your question you seem to have three columns next to each player. Why is that? What are those columns representing?

I propose that the cells in your examples are games, and that's really what you're modelling. You should create a game table with columns such as player1, player2, score1, score2. That would model who played against whom, and what the respective scores were.

You could add further information to each game, for example, when the game was played.

answered Jan 25, 2013 at 15:32

Adrian Smith

17.7k12 gold badges76 silver badges95 bronze badges

2 Comments

10now Over a year ago

I have a table for each player. A table(player) has a column for each player he plays against. That means I only add the scores

Adrian Smith Over a year ago

You can have a table for each player, to store information about them (name etc). Then have a second table representing games between those players.

Collectives™ on Stack Overflow

How to replace NULL with value in MySQL?

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related