I have a database with the following table (see below).
What I need to do is to emty the itype column completely, so that if I do a SELECT itype FROM items; it will return empty set.
I have tried dropping and recreating the itype column, but it just adds the data I want to insert to the end, and even when I recreate it, it is not empty.. I need to overwrite it, or better, start with an empty column.
How can I do that?

mysql> desc items;
+----------------------+--------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------------+--------------+------+-----+-------------------+----------------+
| itemnumber | int(11) | NO | PRI | NULL | auto_increment |
| biblionumber | int(11) | NO | MUL | 0 | |
| biblioitemnumber | int(11) | NO | MUL | 0 | |
| barcode | varchar(20) | YES | UNI | NULL | |
| dateaccessioned | date | YES | | NULL | |
| booksellerid | mediumtext | YES | | NULL | |
| homebranch | varchar(10) | YES | MUL | NULL | |
| price | decimal(8,2) | YES | | NULL | |
| replacementprice | decimal(8,2) | YES | | NULL | |
| replacementpricedate | date | YES | | NULL | |
| datelastborrowed | date | YES | | NULL | |
| datelastseen | date | YES | | NULL | |
| stack | tinyint(1) | YES | | NULL | |
| notforloan | tinyint(1) | NO | | 0 | |
| damaged | tinyint(1) | NO | | 0 | |
| itemlost | tinyint(1) | NO | | 0 | |
| wthdrawn | tinyint(1) | NO | | 0 | |
| itemcallnumber | varchar(30) | YES | | NULL | |
| issues | smallint(6) | YES | | NULL | |
| renewals | smallint(6) | YES | | NULL | |
| reserves | smallint(6) | YES | | NULL | |
| restricted | tinyint(1) | YES | | NULL | |
| itemnotes | mediumtext | YES | | NULL | |
| holdingbranch | varchar(10) | YES | MUL | NULL | |
| paidfor | mediumtext | YES | | NULL | |
| timestamp | timestamp | NO | | CURRENT_TIMESTAMP | |
| location | varchar(80) | YES | | NULL | |
| onloan | date | YES | | NULL | |
| cn_source | varchar(10) | YES | | NULL | |
| cn_sort | varchar(30) | YES | | NULL | |
| ccode | varchar(10) | YES | | NULL | |
| materials | varchar(10) | YES | | NULL | |
| uri | varchar(255) | YES | | NULL | |
| itype | varchar(10) | YES | | NULL | |
| more_subfields_xml | longtext | YES | | NULL | |
| enumchron | varchar(80) | YES | | NULL | |
| copynumber | varchar(32) | YES | | NULL | |
+----------------------+--------------+------+-----+-------------------+----------------+
37 rows in set (0.05 sec)

My sql's pretty rough but maybe something like:

.. hope this will point you in the right direction


cheers

0 members found this post helpful.

Thanks, I tried that already, but then I still have something in my column. I need it to be completely empty.

Maybe it's better to describe what I need to do.
I have a table called biblioitems that contains a column called itemtype.
I need the data from that column in the column itype of the table items.
I have tried a few different ways, but the problem is that mysql just adds it to the column. (It is rather long by now...) I tried dropping and recreating the column, but it still has many fields in it after recreating?? All I need is the data in the itype column.

Ok, I have solved it. With a temporary table and an UPDATE statement, thanks to Yang Yang.
Here's the link;
http://www.kavoir.com/2009/05/mysql-...1#comment-9461