Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2010
    Posts
    53

    Unanswered: Update from inner join

    I've been trying to do this update (which i'm sure should be really simple) but i just keep banging my head against the desk!!!

    i'm writing a store locator and i have two tables:

    table outcodes has the following fields:
    outcode, lat, long

    table stores has the following fields:
    store_id, outcode, lat, long

    all i need to do is to

    update stores.lat = outcode.lat, stores.long=outcodes.long
    where stores.outcode = outcodes.outcode.

    Does that make sense?

    I need to look up the latitude & longitude of a stores in the outcodes table using the stores outcode and update the latitude & longitude of the store accordingly...

    any help much appreciated...

    thanks
    j

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by jx12345 View Post
    update stores.lat = outcode.lat, stores.long=outcodes.long
    where stores.outcode = outcodes.outcode.
    i'll walk you through it

    first of all, please go to da manual and look up the UPDATE syntax

    you will notice that the first two parameters after the word UPDATE are optional -- [LOW_PRIORITY] and [IGNORE]

    after that comes something else, and what i'd like you to do is find out what it is, and try to fix your update statement accordingly

    also, scroll down in that manual page to where they give an example of a joined update

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

  3. #3
    Join Date
    Apr 2010
    Posts
    53
    UPDATE stores, outcodes SET stores.lat = outcodes.lat
    WHERE stores.outcode = outcodes.outcode



    sorry i'm a complete chump...

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    that's excellent, you're on the right track

    now, what about the longitude... ?

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

  5. #5
    Join Date
    Apr 2010
    Posts
    53
    well actually i just cheated and changed the lat to long

    UPDATE stores, outcodes SET stores.long= outcodes.long
    WHERE stores.outcode = outcodes.outcode

    if i wanted to do them all together could i write something like:

    UPDATE stores, outcodes
    SET stores.lat = outcodes.lat,
    stores.long = outcodes.long
    WHERE stores.outcode = outcodes.outcode

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    nice job
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Apr 2010
    Posts
    53
    thanks again!

Posting Permissions

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