Updating multiple rows with a value calculated from another column

Question

I have a table with a row that looks like this:

(2009123148498429, '...', '...')

The first part, id, is a timestamp followed by a random number. (needed to work with other parts in the system) The data already exists in the table.

I want to create a column, timestamp, and extract just the date (20091231) and update all the rows with the timestamp.

1. How can I do this for all the rows with SQL? (i.e. update them all with some sort of a function?)
2. What kind of default value should I assign the column to make sure that future inserts correctly extract the date?

UPDATE - Please read the comments by bobince in the first answered question by Jonathan Sampson on how we got to the answer. This is the final query that worked:

UPDATE table SET rdate=substring(convert(rid,char(20)),1,8);

The problem was that I was using substring as substring( str, 0, 8 ) whereas it should be substring( str, 1, 8 ). I guess we're all used to 0 as the beginning position! More info here on substring

Related to: multiple updates in mysql

Answer 1

SELECT SUBSTRING(colDate,0,8) as 'date' 
FROM someTable

Or am I mistaken?

UPDATE someTable
SET newDateField = SUBSTRING(colDate,0,8)

Would likely work too. Untested.

Answer 2

Use a sub-select in your update (untested, and I've been using Firebird for too long, so someone check me here).

UPDATE MyTable AS TUpdate
SET MyNewField = (SELECT SUBSTRING(TSub.MyDateColumn,0,8) 
                  FROM MyTable AS TSub 
                  WHERE TSub.MyID = TUpdate.MyID);

As for future inserts correctly extracting the date, you're going to have to create a trigger on insert to extract the date for that record.

Answer 3

Need to use a subselect.

UPDATE someTable set timestamp = (SELECT SUBSTRING(colData, 0, 8) FROM someOriginalTable);

EDIT lc got me by a few seconds!

Answer 4

UPDATE tbl
SET
   newDateField = SUBSTRING(CAST(sourceCol AS varchar), 0, 8)