CODEWITHSUNDEEP
mysqlsql
StandardNerd
StandardNerd

Reputation: 4183

mysql change all values in a column

I want to change all values in the tablecolumn "Quellendatum".

When the row-value is 2005-06-20 then it should be replaced with 2012-06-20. When the row-value is NULL or empty, then it should be untouched.

Currently i modify this manually by selecting the row:

UPDATE  `outgoing2`.`tbl_hochschule` 
SET  `Quellendatum` =  '2012-06-20' 
WHERE  `tbl_hochschule`.`id` =1;

Is there a way to automate this task?

Upvotes: 34

Views: 118283

Answers (6)

Yahya Alami
Yahya Alami

Reputation: 47

If Quellendatum = "2005-06-20" then it can't be NULL. So I don't see the use of ' AND !isnull (Quellendatum)

Upvotes: 0

Narain C A
Narain C A

Reputation: 19

You can try

UPDATE TABLE 
SET COLUMN_NAME = " "

This will update all the values in a column

Upvotes: 0

LucianoDemuru
LucianoDemuru

Reputation: 131

In MySql you can do:

UPDATE TABLENAME    
SET IDCOLUMN=VALUE    
WHERE IDCOLUMN=VALUE    
AND !isnull (IDCOLUMN)

Upvotes: 12

Kautil
Kautil

Reputation: 1331

UPDATE outgoing2.tbl_hochschule 
SET Quellendatum = '2012-06-20' 
WHERE Quellendatum <> '' AND Quellendatum <> NULL;

Upvotes: 0

Devang Rathod
Devang Rathod

Reputation: 6736

it should be :

UPDATE tablename 
SET Quellendatum = '2012-06-20' 
WHERE Quellendatum = '2005-06-20'

Upvotes: 6

ethrbunny
ethrbunny

Reputation: 10469

How about:

UPDATE outgoing2.tbl_hochschule 
SET Quellendatum = '2012-06-20' 
WHERE Quellendatum = '2005-06-20' 
AND !isnull( Quellendatum );

Upvotes: 37

Related Questions