dingo_d February 2016

Error while updating table value

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?

Answers


Ullas February 2016

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;


Code-Monk February 2016

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;


Max Bongiorno February 2016

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

Post Status

Asked in February 2016
Viewed 2,800 times
Voted 10
Answered 3 times

Search




Leave an answer