Exclude a Column from a mysql command

[lorddemos90]

Is there a way to exclude one specific column in a mysql database from a command.  IE: I want to use the command

$sql = "TRUNCATE TABLE DailyDataSF_copy";  But have it affect all columns except just one specified column that'll stay the same.  Any solutions to this?  The table has like 90+ columns, which is why I'd like to do it this way if at all possible.

[utexas_pjm]

Truncating a table drops all rows.  It has nothing to do with altering the table structure.  Are you trying to remove the columns from the table or are you trying to "unset" the data in the columns?

 

[lorddemos90]

I want to just delete all the data from the table except for one column.

[utexas_pjm]

Then you want syntax like this:

 

UPDATE `your_table` SET `col_a` = NULL, `col_b` = 0... etc..

 

Some columns "erased" state are null while others are zero so you'll have to specify each one individually.

 

Best,

 

Patrick

[per1os]

One possible approach is do a describe on the table and from that data returned loop through it and do an update on each column name or use that to build a query on the fly. That way you do not have to type in the columns.

 

Not sure how it would work or if it would work but it might be something similiar to this:

 

<?php
$res = mysql_query("describe table_name;");

while ($row = mysql_fetch_assoc($res)) {
    if ($row['column_name'] != "90thcolumnname")
          $update .= "`".$row['column_name']."` = NULL,";
}

$update = "Update `table_name` SET " . substr($update, -1);
mysql_query($update);
?>

 

Note I am not sure of the "column_name" portion or if the describe will return a result array. But something like that should work.