UPDATE each item in a column

views:

answers:

UPDATE each item in a column

I'm having trouble figuring out a solution to this. I have several tables in my database with attributes having a date type. However, in one of my tables I was not thinking during the design process so the attribute is not of date but is a varchar. The dates in the "incorrect" table is formatted as dd-MMM-yyyy whereas all of the other dates are formatted as yyyy-mm-dd.

How can I run through the "incorrect" column and do CAST(myDate AS date) on each mis-typed attribute?

+1 A:

I'd migrate to a date column.

Add a nullable date column
Run an update query to set the value of each column to the result of your cast
Drop the varchar column
Refactor your code or rename the new column to the name of the old one

Phil Brown 2010-10-25 05:40:24

That got it. I was initially creating an entirely new table and trying to copy casted contents into it, then copy them back to the old table. Was running into errors with that. This worked like a charm. Thanks.

dc 2010-10-25 05:51:07

Alternately, you can do this: "ALTER TABLE <table> ALTER COLUMN <column> TYPE DATE USING <column>::DATE;" to do it all in one step.

Matthew Wood 2010-10-26 19:26:14

ansaurus

tags:

views:

answers:

UPDATE each item in a column

related questions