MySQL:  Update Query help

Posted on 2014-07-16
Last Modified: 2014-07-21
Hi All,

I have two tables likes and movies

likes:  id, movie_id, link_id
movies:  id, link_id

I want to update all the likes.movie_id with the where the link_id's match

Many thanks
Question by:detox1978
    LVL 7

    Expert Comment

    by:Raghu Mutalikdesai
    UPDATE likes SET likes.movie_id = FROM likes, movies WHERE likes.link_id = movies.link_id;

    Open in new window

    LVL 16

    Expert Comment

    by:Wasim Akram Shaik
    you should try something like this

    UPDATE t1
      SET t1.movie_id= t2.[id]
      FROM likes AS t1
      INNER JOIN movies AS t2
      ON t1.link_id= t2.[link_id]
    LVL 2

    Accepted Solution

    I found a sample of the proper syntax online here

    UPDATE `Catalogue` 
    INNER JOIN `Categories` 
      ON Catalogue.Category_ID = Categories.Category_ID 
      AND Categories.Category_Name = 'Bases'
    SET `Visible` = '0' 

    Open in new window

    LVL 107

    Expert Comment

    by:Ray Paseur
    Whatever you want to do with these tables, the WHERE clause would contain this:

    WHERE likes.link_id = movies.link_id

    Since UPDATE is not an idempotent query, but creates no new rows, you can rerun the query repeatedly without any problems.  You might want to run two queries - one to SELECT and a separate one to UPDATE.  How many rows are in these tables?  Have you tried any code yet?  If so, please show us, thanks.
    LVL 2

    Author Closing Comment

    found solution online

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Join & Write a Comment

    Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL ( several years ago, it seemed like now was a good time to updat…
    These days socially coordinated efforts have turned into a critical requirement for enterprises.
    Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
    The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now