Updating multiple rows with a value calculated from another column

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

From stackoverflow

• 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.

  Swati : ...I also want to be able to insert it right back into the table. In all the rows.

  Jonathan Sampson : The UPDATE option I added might help there.

  Swati : This is not working. I keep getting "query returned no resultset". Could it be because the 'colDate' (i.e. 2009123148498429) is stored as a BIGINT and not a string?

  bobince : +1. I don't understand why people are jumping straight for the sub-selects in what would appear to be, unless I've missed something, an absolutely straightforward UPDATE task.

  Ben Alpert : Try colDate/100000000 instead.

  Swati : @Ben: the length of the random number varies...so that doesn't work.

  bobince : Ah OK, if you need int to string use CONVERT. You get a free truncate whilst you're at it: “SET newDateField = CONVERT(colDate, CHAR(8))”

  Swati : A new error now with CONVERT(colDate, CHAR(8)): Truncated incorrect char(8) value:'200910101...'

• 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.

  Swati : Not working either. I am getting complaints about using AS in the FROM clause...which is peculiar. Maybe they aren't allowed in UPDATE statements?

  lc : That's odd, but you could try without. It might be able to disambiguate the table in the sub-select from TUpdate if you can get the TUpdate alias to work...

• Need to use a subselect.

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

  EDIT lc got me by a few seconds!

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