Put value in everything that is null in mysql

Navigation
0

I have the following problem, I created a table in mysql I added all the records and after that I created a foreign key in it, in this table it contains information of only one "thing", however in the future it will have more things. I would like to know if there is a query that replaces everything that is Null by another value, it is no use changing the default because in the future this value will have to change

    
asked by anonymous 15.01.2015 / 18:37

2 answers

4

From what I understood, you had something like this: 

TabelaX
|--------------------|
| ColA | ColB | ColC |
|------|------|------|
| 1    | 'ab' |  3   |
| 2    | 'ac' |  3   |
| 3    | 'ad' |  5   |
|--------------------|

You have created a new column and the existing values are with NULL ; 

TabelaX
|---------------------------|
| ColA | ColB | ColC | ColD |
|------|------|------|------|
| 1    | 'ab' |  3   | null |
| 2    | 'ac' |  3   | null |
| 3    | 'ad' |  5   | null |
|---------------------------|

You want to change to a specific value, just use: 

UPDATE TabelaX SET ColD = 1;

If you can not, the bank server may have secure update mode enabled (that is, you need a where clause for update to work), you could use 1=1 that will always be true and will update all records. 

UPDATE TabelaX SET ColD = 1 WHERE 1=1;

Result: 

TabelaX
|---------------------------|
| ColA | ColB | ColC | ColD |
|------|------|------|------|
| 1    | 'ab' |  3   |  1   |
| 2    | 'ac' |  3   |  1   |
| 3    | 'ad' |  5   |  1   |
|---------------------------|
    
15.01.2015 / 18:51
2

The command to update records is UPDATE , which has the following syntax: 

UPDATE nome_tabela
SET CAMPO = "novo_valor"
WHERE campo is null

Do this when you need to update the fields that are null, which is your case.

    
15.01.2015 / 18:48
How to compare an inserted phrase with those in the DB returning the probability of being similar? Browsing entire DOM with jQuery