CODEWITHSUNDEEP
mysqlsql
nkumar
nkumar

Reputation: 89

Single mysql Query: To make NULL value of tables & there column's in database

Is this possible with single query we can make all Columns and rows NULL or Zero(0) as its value ?

I have tried but failed to do with 2 3 tables in a Database; to make them NULL.

Upvotes: 0

Views: 131

Answers (3)

nkumar
nkumar

Reputation: 89

$sql="SELECT CONCAT('UPDATE ', TABLE_SCHEMA,'.', TABLE_NAME, ' SET ',COLUMN_NAME,' = 0;') AS MySQLCMD FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'DATABASE_NAME' ";

$result=$conn->query($sql);
if($result->num_rows> 0){
while($row=$result->fetch_assoc()){
    $exe=$conn->query($row['MySQLCMD']);
}

}

Here's the Answer for what i need. Thanks alot for all your precious suggestions. And special thanks to : angel.bonev

Upvotes: 0

angel.bonev
angel.bonev

Reputation: 2232

To update multiple tables in mysql you can use this: 

UPDATE table1 , table2 SET table1.column = 0 , table2.column =0

If you don't know the fields you can generate an query to get all the sub-queries that you need

SELECT CONCAT("UPDATE `", TABLE_SCHEMA,"`.`", TABLE_NAME, "` SET `",COLUMN_NAME,"` = 0 ") AS MySQLCMD FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = "you_db_name_here"

now you have all the queries to 0 all the columns in all the tables in database, so now what you can use PREPARE EXECUTE

SET @query :="";
SELECT @query := CONCAT("UPDATE `", TABLE_SCHEMA,"`.`", TABLE_NAME, "` SET `",COLUMN_NAME,"` = 0 ") AS MySQLCMD FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = "you_db_name_here";
PREPARE stmt FROM @query;
EXECUTE stmt;

This will not work if you have primary keys, we need to skip them and the final result will look like this

SET @query :="";
SELECT @query := CONCAT("UPDATE `", C.TABLE_SCHEMA,"`.`", C.TABLE_NAME, "` SET `",C.COLUMN_NAME,"` = '' ") AS MySQLCMD from information_Schema.Columns C LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE U ON C.COLUMN_NAME = U.COLUMN_NAME WHERE C.TABLE_SCHEMA = "you_db_name_here" AND U.CONSTRAINT_NAME is NULL;
PREPARE stmt FROM @query;
EXECUTE stmt;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270513

Perhaps this is what you want or not. I don't understand why you would want to set column values in existing rows to all NULL or 0. Wouldn't it be better just to empty the table, so there are no rows? Then you can add new rows that you want.

To empty a table efficiently, use truncate:

truncate table t;

If you want to set all columns in a table to value, just use an update and list all the columns:

update t
    set col1 = null,
        col2 = null,
        . . .;

However, I don't see why this would be useful.

Upvotes: 1

Related Questions