datestamp the purchase_date field for all rows

nickweavers · #1 (**permalink**) 07-10-07, 18:51

I know it is simply enough to get a bunch of rows from a table and loop through them in PHP to change thesame field in all rows, eg. set them to NOW(), but can this be done purely in MySQL with a query. Can subqueries perform changes to the table the main query is operating on yet?

For instance, I want to datestamp the purchase_date field for all rows matching my WHERE clause with todays datetime. Would be neat to have a single query to do this.

Thanks,
Nick

nickweavers · #2 (**permalink**) 07-10-07, 18:54

I know it is simply enough to get a bunch of rows from a table and loop through them in PHP to change the same field in all rows, eg. set them to NOW(), but can this be done purely in MySQL with a query. Can subqueries perform changes to the table the main query is operating on yet?

For instance, I want to datestamp the purchase_date field for all rows matching my WHERE clause with todays datetime. I guess it could be called a column update. Would be neat to have a single query to do this.

Thanks,
Nick

nickweavers · #3 (**permalink**) 07-10-07, 18:56

Apologies, I meant to put that in a new thread.

r937 · #4 (**permalink**) 07-10-07, 19:28

no apologies necessary, the friendly moderators are here to help you

can this be done purely in MySQL with a query?

yes

r937 · #5 (**permalink**) 07-10-07, 19:30

see? we can even merge threads

nickweavers · #6 (**permalink**) 07-10-07, 19:41

Could you please show me how?

Thanks,
Nick.

r937 · #7 (**permalink**) 07-10-07, 19:55

Quote:

Originally Posted by nickweavers

I want to datestamp the purchase_date field for all rows matching my WHERE clause with todays datetime.

i will have to guess at a lot of this, because you did not give too many details...

Code:

update daTable
   set purchase_date = current_date
 where somecolumn = 'foo'
   and someothercolumn = 937

aschk · #8 (**permalink**) 07-11-07, 03:35

A possible solution? :

1) Add a timestamp column to your table

Code:

ALTER TABLE <tablename> 
ADD COLUMN `update_datetime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP

2) As in Rudy's query (except with a minor change) :

Code:

UPDATE <tablename>
   SET update_datetime = NULL
   WHERE somecolumn = 'foo'
   AND someothercolumn = 937

The advantage of using a TIMESTAMP column type is that if you choose to update another column (say `name`) the update_datetime column will automagically update itself with the latest datetime....

r937 · #9 (**permalink**) 07-11-07, 05:47

ah, but the problem with using a timestamp—or a datetime, for that matter—is that when you then go to write a query to pull all the inventory items purchased yesterday, you cannot write the query using

... where purchase_date = date_sub(current_date,interval 1 day)

aschk · #10 (**permalink**) 07-11-07, 08:48

Surely you could just change the "=" to a ">"

Or if you want to rule out todays updated also then

Code:

... WHERE purchase_date 
BETWEEN date_sub(current_date,interval 1 day) 
AND CURRENT_DATE;

r937 · #11 (**permalink**) 07-11-07, 08:50

yes, you could, and please don't call me shirley

but i do hope you see my point -- why assign a timestamp or datetime value to something that is essentially a date

aschk · #12 (**permalink**) 07-11-07, 09:01

Yup i shirley

do see your point. I suppose it depends on how granular the updated information needs to be.

Also came across another useful feature (ONLY AVAILABLE SINCE version 5.0.0)

Code:

WHERE 1 =
TIMESTAMPDIFF(DAY,CURRENT_DATE,purchase_date);

Checks the number of days between a timestamp and datetime. Pretty cool.

r937 · #13 (**permalink**) 07-11-07, 09:21

pretty cool but really really slow

Internet Services

Web Development

Digital Marketing

Consumer Tech