SQL syntax error INNER JOIN

Question

I'm trying to run this sql query and keeps getting this error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INNER JOIN wp_posts ON wp_postmeta.post_id = wp_posts.ID WHERE wp_posts.post_typ' at line 3 (Line 4)

My sql query is this:

UPDATE wp_postmeta
SET meta_value = "1316"
INNER JOIN wp_posts ON wp_postmeta.post_id = wp_posts.ID
WHERE wp_posts.post_type="imagen_dia" AND wp_postmeta.meta_key="ae_post_template"

Any help please?

Which DBMS? Oracle? MySQL? SQL Server? ... The syntax for joined updates differs a lot in the various DBMS. Double quotes are for names by the way; use single quotes for strings. — Thorsten Kettner, Nov 20 '17 at 20:11
Possible duplicate of [MYSQL Update Statement Inner Join Tables](https://stackoverflow.com/questions/8057565/mysql-update-statement-inner-join-tables) — jmoerdyk, Nov 20 '17 at 20:13

score 2 · Accepted Answer · answered Nov 20 '17 at 20:10

2

In mysql the JOIN should be before the set

UPDATE wp_postmeta
INNER JOIN wp_posts ON wp_postmeta.post_id = wp_posts.ID
SET meta_value = "1316"
WHERE wp_posts.post_type="imagen_dia" AND wp_postmeta.meta_key="ae_post_template"

answered Nov 20 '17 at 20:10

ScaisEdge

131,976
10
91
107

1

THANKS A LOT!!! Now it works perfectly!! I was using sql all the time not knowing I have to use mysql. You saved my day. Thanks again my friend!! :) – Bob Nov 20 '17 at 20:18

score 1 · Answer 2 · answered Nov 20 '17 at 20:14

The syntax for joined updates is vendor-specific. So do this without a join to have the update statement simple, readable and safe :-)

UPDATE wp_postmeta 
SET meta_value = '1316'
WHERE meta_key = 'ae_post_template'
AND post_id IN (SELECT id FROM wp_posts WHERE post_type = 'imagen_dia');

This statement is standard SQL and should work in about every RDBMS.

score 0 · Answer 3 · answered Nov 20 '17 at 20:10

0

You are missing the FROM word:

UPDATE wp_postmeta
SET meta_value = "1316"
FROM wp_postmeta
INNER JOIN wp_posts ON wp_postmeta.post_id = wp_posts.ID
WHERE wp_posts.post_type="imagen_dia" AND wp_postmeta.meta_key="ae_post_template"

answered Nov 20 '17 at 20:10

Matt Spinks

6,380
3
28
47

SQL syntax error INNER JOIN

3 Answers3