0

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.

Improve this question
8
  • 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. Commented Aug 15, 2015 at 3: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 Commented Aug 15, 2015 at 3:17
  • you most definitely can do this. Commented Aug 15, 2015 at 3:19
  • Do you agree that using transactions as opposed to Inner join is the best way to go about this? @ethorn10 Commented Aug 15, 2015 at 3:21
  • it can be done with an inner join Commented Aug 15, 2015 at 3:23

1 Answer 1

Reset to default
1

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
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.