Looping with Databases

[flybishop]

All I'm trying to do is loop through every record in my database while changing the value of every row in a particular column. I'm sure it's an insert into. Basically I added a new column called Date and I want to change all 2100+ rows to equal todays date. Please help cause I'm sure it's easy.

[ahundiak]

An update without a where clause will update all records in a table. You normally don't want that but in this case it sounds like all you need.

UPDATE table SET date='whatever';

[Sxooter]

We had something similar, but it was required we set each date to some randomly determined date in a 30 day window so that the effect wouldn't avalanche when the expiry came.

I just wrote a script that grabbed a list of ids, did a for loop across them all and issues an "update table set date='randomdatehere' where id=$id" thing.

But if having the same date in them all is ok, and it usually is, then just do what ahundiak says and update them all at once. Note that if you've already got SOME dates you wanna keep, and all the others are set to NULL, you can add a "where date IS NULL" to the end of your query.

Note you should ALWAYS say IS NULL, not = NULL, as nothing can actaull equal NULL, and the SQL spec says to forbid it. Therefore some databases will choke on = NULL.