Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2011
    Posts
    85

    Unanswered: Update with different tables

    Hello Experts

    I have a problem with update statement

    I have two tables

    MovieStar , StarsIn

    MovieStar - name,address,birthdate,gender,rating
    StarsIn - movieTitle,movieyear,starname,role

    so I want to update the MovieStar - rating field

    If StarsIn.role = 'Support'

    Basically MovieStar.name and StarsIn.starname is the name of the actor

    So if he act a supporting role the i want to update the rating+1

    Any ideas

    This was done by me..
    But its wrong

    update moviestar
    set rating = rating+1
    where moviestar.name=starsin.starname
    and starsin.role = 'Support'

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Try this:

    UPDATE moviestar m
    JOIN starsin s ON m.name = s.starname
    SET moviestar.rating = moviestar.rating+1;

    and starsin.role = 'Support'
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    UPDATE moviestar 
    INNER
      JOIN starsin  
        ON starsin.starname = moviestar.name
       AND starsin.role = 'Support'
       SET moviestar.rating = moviestar.rating+1
    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
  •