SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Member
    Join Date
    May 2008
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    UPDATE with subselect?

    I have two tables in my database

    table1 holds a lot of info and table2 holds some metadata.

    Especially I want to store the first and last (order by time) value from table1 in table2 pr. day and per key.

    Say

    Let's say table1 has the fields: id, datetime, key, value (AND MORE)

    table2 has the fields: id, date, key, firstvalue, lastvalue (AND MORE)

    I have a query that creates the rows in table2 already, but with firstvalue and lastvalue blank.

    In PHP I could:

    SELECT key, MIN(datetime) and MAX(datetime) GROUP BY LEFT(datetime,10), key

    Iterate over my array, - find the value of each
    UPDATE my table2

    But that would be quite a few SELECT and UPDATE queries.

    Can someone help me do this in just a few (two?) SQL queries? (I guess I'm goint to need one for the max and one for the min)

  2. #2
    SitePoint Wizard
    Join Date
    Dec 2003
    Location
    USA
    Posts
    2,582
    Mentioned
    29 Post(s)
    Tagged
    0 Thread(s)
    If you are using MySQL 5+, this should work:
    Code:
    UPDATE table2 t 
    SET first = 
        (SELECT key, MIN(datetime) 
         GROUP BY LEFT(datetime,10), key) 
         WHERE id = t.id), 
    last = 
        (SELECT key, MAX(datetime 
         GROUP BY LEFT(datetime,10),key 
         WHERE id=t.id)
    Basically, to put it in one query you had it backwards. Do the UPDATE, and use a sub-select inside of it to get the data you want. That may not be the exact syntax, but you get the idea.

    Hope that helps.

  3. #3
    SitePoint Member
    Join Date
    May 2008
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks a lot samanime,

    you're right. It's not the exact syntax but it did give me what I want.
    Thanks!


Bookmarks

Posting Permissions

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