How to UPDATE multiple tables with 1 query in MYSQL

Question

I was wondering if it's possible to update multiple tables with one query with mysql. I have tried using INNER JOIN, but I believe that I am off because I continue to get syntax error after syntax error. Below is my current sql syntax:

UPDATE blog_posts a INNER JOIN search b ON a.postTitle =  b.title, a.postSlug = b.link, a.postDesc = b.description, a.postCont SET a.:postTitle = b.:postTitle, a.:postSlug = b.:postSlug, a.:postDesc = b.postDesc, a.:postCont WHERE postID = :postID AND title = :postTitle

Here is some background: I have two tables in the same database. One is named blog_posts, and the other search. My blog_posts table contains the content for the blog posts on my website. I recently added a search engine to my site, and added the table search to store all of my pages' data. My idea is that everytime someone updates a blog post, the info in the search table will be updated as well with that same info. I know for a fact that my syntax is off, but I would appreciate if someone could help me get it in tact so that it does what I described above.

Also, my tables each have different column names. blog_posts has the following: postID, postTitle, postSlug (the title converted to a link), and postDesc search has the following: id, title, description, keywords, and link

I have tried to make my script so that it updates the search table row where the postTitle = title. Maybe there's a better way to do this? Anyway, any help would be appreciated.

No, you aren't able to update multiple tables with MySQL joins. You can use transactions though, to automatically update other tables to keep everything in sync. Personally, I've always handled this pragmatically. — terribleuser, Aug 15 '15 at 03:16
Oh I read that joins would update multiple tables. Thank you though. Any sites in mind that may lead me in the right direction? @terribleuser — The Creator, Aug 15 '15 at 03:17
Do you agree that using transactions as opposed to Inner join is the best way to go about this? @ethorn10 — The Creator, Aug 15 '15 at 03:21
So it is possible. Could you give me an example as an answer? @ethorn10 — The Creator, Aug 15 '15 at 03:24
not only can this be done, it has been asked and answered before. — e4c5, Aug 15 '15 at 03:29
Truth. I didn't realize I could go without inner join @e4c5 Thank you son — The Creator, Aug 15 '15 at 03:34

score 1 · Accepted Answer · answered Aug 15 '15 at 03:36

You can do this without the explicit INNER JOIN syntax, but instead do it in your WHERE clause:

UPDATE blog_posts a, search b
SET a.postTitle = :postTitle
    , b.title = :postTitle
    , a.postSlug = :postSlug
    , b.link = :postSlug
    , a.postDesc = :postDesc
    , b.description = :postDesc
-- this continues for any tables/values you want to set
WHERE a.postID = :postID
    AND -- whatever else joins your two tables together or other unique values

How to UPDATE multiple tables with 1 query in MYSQL

1 Answers1