Error while updating table value

Question

I have 2 tables: table1 and wp_posts. The first one is a custom table, the second one a wordpress one.

I want to change all post types in wp_posts that match a criteria that the post_title is the same as the headline in the table1 and that the type_id key equals 41.

I tried selecting those kind of posts

SELECT * FROM wp_posts LEFT JOIN table1 ON table1.headline = wp_posts.post_title WHERE table1.type_id = 41

And I get correct posts back (I'm doing this in phpmyadmin).

So I tried with

UPDATE wp_posts SET wp_posts.post_type = 'jobs' FROM wp_posts JOIN table1 ON table1.headline = wp_posts.post_title WHERE table1.type_id = 41

And I get an error

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'FROM wp_posts JOIN table1 ON table1.headline = wp_posts.post_title W' at line 1

The table look like this (roughly)

table1

headline  |  type_id
--------------------
Random    |  41
Random 2  |  41

wp_posts

post_title  |  post_type
--------------------
Random      |  post
Random 2    |  post

And I need to change post to jobs

post_title  |  post_type
--------------------
Random      |  jobs
Random 2    |  jobs

The names (post_title and headline) are unique, so there won't be duplicates etc.

What's wrong with my ALTER sql statement?

Update syntax is `Update table set column=value where conditions ` — undefined_variable, Feb 08 '16 at 10:09
I'm a beginner at mysql (actually I was thrown into it, not my field primarily), so I don't know the proper way to do it. Thanks for the clarification. — dingo_d, Feb 08 '16 at 10:14

score 1 · Answer 1 · answered Feb 08 '16 at 10:09

1

Change the update query like below

Query

UPDATE wp_posts
JOIN table1
ON table1.headline = wp_posts.post_title
SET wp_posts.post_type = 'jobs'
WHERE table1.type_id = 41;

answered Feb 08 '16 at 10:09

Ullas

11,450
4
33
50

This is the same as the above, but he was first, so I'll accept his. Thanks +1 – dingo_d Feb 08 '16 at 10:15
@dingo_d : We both answered exactly at the same time. Anyway no problem.. Thanks – Ullas Feb 08 '16 at 10:16
Yeah, but when you sort by oldest tab, his comes first... no hard feelings :) – dingo_d Feb 08 '16 at 10:26

score 1 · Accepted Answer · answered Feb 08 '16 at 10:09

1

Try this:

UPDATE wp_posts JOIN table1 ON table1.headline = wp_posts.post_title
SET wp_posts.post_type = 'jobs' 
WHERE table1.type_id = 41;

answered Feb 08 '16 at 10:09

Abhishek Ginani

4,511
4
23
35

This did the trick. Thanks. I'll accept it in 8 minutes – dingo_d Feb 08 '16 at 10:12

score 0 · Answer 3 · answered Feb 08 '16 at 10:13

Update Statement pattern is :

UPDATE [LOW_PRIORITY] [IGNORE] table_reference
SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]

The error thrown is caused by your 'FROM' key word.

You cannot ask for an update and in the same time use the SELECT Pattern data.

UPDATE TABLE_NAME SET TABLE_NAME.COLUMN_NAME = 'MY CUSTOM DATA' 
WHERE TABLE_NAME.[OTHER]COLUMN_NAME = 'MY PATTERN DATA';

MYSQL Link : http://dev.mysql.com/doc/refman/5.7/en/update.html

Thanks for the info, I'll look into it. – dingo_d Feb 08 '16 at 10:14 — dingo_d, Feb 08 '16 at 10:14

Error while updating table value

3 Answers3