CODEWITHSUNDEEP
phpmysqlsqlreplace
dawson0707
dawson0707

Reputation: 1

MySQL - replace space in whole table in all rows and columns

Ok, maybe this is really simple, but I can´t figure it out by myself:

First I have a csv file. I´m importing this with LOAD DATA LOCAL INFILE ... into a mysql table with 20 columns and over 10000 rows.

Then there are fields with content = " " in some rows and columns that I wanted to replace with an "". I don´t know, in what columns a " " will be, so I want to iterate through the whole table.

So I thought on something like this: REPLACE INTO products (XXX) VALUE ("") WHERE XXX = " " ... where XXX are all column-names.

Can somebody give me hint for this? Maybe I should do it within php? Thanks.

[Edit] Or maybe there is a way to do it while importing the csv-file itself?

[Edit] I just thought on doing a UPDATE table SET column = "" WHERE column=" ". But I want this without writing 20 querys for every single column.

Upvotes: 0

Views: 155

Answers (2)

dawson0707
dawson0707

Reputation: 1

Didn´t get the INFORMATION_SCHEMA to work so I tried this:

$rs = $db->query('SELECT * FROM mytablename LIMIT 0');

for ($i = 0; $i < $rs->columnCount(); $i++) {
    $col = $rs->getColumnMeta($i);
    $columns[] = $col['name'];
    $rs2 = $db->query('UPDATE mytablename SET '.$col['name'].'="" WHERE '.$col['name'].'=" "');
}

It worked out well.

Upvotes: 0

Namphibian
Namphibian

Reputation: 12221

As Mihai mentioned you can use the INFORMATION_SCHEMA tables to generate the SQL To execute.

Use the following script to generate the SQL:

SELECT CONCAT('UPDATE ', TABLE_NAME ,' SET ',COLUMN_NAME ,'=\"\" WHERE ', COLUMN_NAME  ,'=\" \";') AS  SQLCOMMAND
FROM INFORMATION_SCHEMA.COLUMNS
WHERE data_type IN ('VARCHAR' ,'CHAR')
AND TABLE_SCHEMA='your_database_name'

This will print out a update statement for all columns that is VARCHAR or CHAR and update those column values to "".

Upvotes: 1

Related Questions