Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2004
    Posts
    3

    Unanswered: UPDATE with two tables and FOREIGN KEY

    I have database which has two tables:
    CREATE TABLE sport (idsport INT, name VARCHAR(20), PRIMARY KEY (idsport));
    INSERT INTO sport (idsport, title) VALUES (1,'football'), (2,'hockey'), (3,'box');

    CREATE TABLE adressbook (idperson INT, name VARCHAR(30), idsport INT, PRIMARY KEY(idperson),
    FOREIGN KEY(idsport) REFERENCES sport(idsport));
    INSERT INTO adressbook (idperson,name,idsport) VALUES (1,'Tom', 2);

    And now
    I need with one command UPDATE change row in table adressbook, that way in column idsport I enter title e.g. 'box'. I mean something like this:
    UPDATE adressbook SET name='Tomas, idsport='Box' WHERE idperson=1;
    Of course this doesn`t work, because idsport is FOREIGN KEY.
    How make it?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    UPDATE adressbook, sport
    SET adressbook.name = 'Tomas'
    , adressbook.idsport = sport.idsport
    WHERE adressbook.idperson=1
    and sport.title='box'
    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
  •