SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Is this do-able?

    Hi,

    Is it possible to select, mathematise and insert all in one query, or need I do it in my server langauge of choice?

    firstly, I need to select and then insert as shown below.

    Code MySQL:
    select longitude
           , latitude
      from table
     
    UPDATE address 
         SET latitude_miles = 69.1*latitude, 
             longitude_mpd = 69.1*cos(latitude*3.14159265/180);

    bazz

  2. #2
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Code:
    UPDATE 
      address, table
    SET
      address.latitude_miles = 69.1 * table.latitude,
      longitude_mpd = 69.1 * COS(table.latitude * 3.14159265 / 180)
    WHERE
      something relates the `address` and `table` tables
    This is just an UPDATE query that uses multiple tables right?

  3. #3
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I got it.
    Code MySQL:
     UPDATE address SET latitude_miles = 69.1 * latitude,
    longitude_mpd = 69.1 * COS( latitude * 3.14159265 /180 );
    SELECT a.latitude, a.longitude
    FROM address AS a

    I added a ; which I thought would break the query into two but, it worked.

    bazz

  4. #4
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    We almost cross-posted Dan, so I 'll study your query for future reference. It seems easier to understand than mine.

    Thanks

    bazz

  5. #5
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Are you just asking how to use the MySQL client? Yes a semicolon ends a query and you can type multiple queries at the same time.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,214
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by IBazz View Post
    I got it.
    i don't think you do

    what you show there is an UPDATE followed by a SELECT, but where does the UPDATE get its values from?

    dan's query is a joined update which allows values from one table to update the values in another table

    it isn't just "easier to understand" but also the only way to do what you're asking

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks both of you.

    Yes, I was asking how to use the MySQL window to do the update where the query had to get the basic values and then manipulate them before updating.

    I ran the 'script' I posted and whilst it showed two empty cols in phpMyAdmin, it did do the update.

    I'll go back over it again and make sense of Dan's post.

    Thanks again.

    bazz


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
  •