SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Zealot
    Join Date
    Nov 2006
    Posts
    166
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Update foreign key values in parent table

    I have a member table that has all the standard info (name, address, phone number, etc.) and then I have foreign key ids referencing the related gender, country of the member

    When I'm writing an update sql statement, I want to update the gender and country ids in the member table by entering their corresponding value in both the gender table(eg.male/female) and country table(eg.ireland/england/france/etc.)
    I'm doing this as I want it to make sense when entering values rather than entering numeric data.

    If anyone knows how this is acheived that would be great.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by seannie View Post
    When I'm writing an update sql statement, I want to update the gender and country ids in the member table by entering their corresponding value in both the gender table(eg.male/female) and country table(eg.ireland/england/france/etc.)
    Code:
    UPDATE members
       SET genderid =
           ( SELECT id 
               FROM genders
              WHERE gendername = 'male' )
         , countryid =  
           ( SELECT id 
               FROM countries
              WHERE countryname = 'canada' )  
     WHERE id = 937
    although why you're doing this on an UPDATE instead of on the original INSERT is weird
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •