Results 1 to 13 of 13
  1. #1
    Join Date
    Jan 2005
    Posts
    15

    Unanswered: datestamp the purchase_date field for all rows

    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

  2. #2
    Join Date
    Jan 2005
    Posts
    15

    Column update?

    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

  3. #3
    Join Date
    Jan 2005
    Posts
    15
    Apologies, I meant to put that in a new thread.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    no apologies necessary, the friendly moderators are here to help you

    can this be done purely in MySQL with a query?

    yes

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    see? we can even merge threads
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jan 2005
    Posts
    15
    Could you please show me how?

    Thanks,
    Nick.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    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....

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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)

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    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;
    Last edited by aschk; 07-11-07 at 09:52.

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    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.

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    pretty cool but really really slow

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •