How to assign a variable to select and update a field with it

Question

I have two columns in my table, and I am trying to update the second column with the result of first col like this.

SET @pg = (SELECT ID FROM wp_posts WHERE post_name = 'y' AND post_status = 'x' LIMIT 1)

Now the above query works the (SELECT ...) as in it returns an ID from the query, so I am trying to use that ID to update another column like this.

UPDATE wp_posts SET post_type = 'foo' WHERE ID = @pg;

But for some reason the above does not work.

and I get the following error.

1 queries executed, 0 success, 1 errors, 0 warnings

Query: set @pg = (SELECT ID FROM wp_posts WHERE post_name = 'y' AND post_status = 'x' LIMIT 1) SELECT * ...

Error Code: 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 'SELECT * FROM wp_posts WHERE id = @pg' at line 3

Execution Time : 0 sec
Transfer Time  : 0 sec
Total Time     : 0 sec

EDIT/UPDATE:

I am also trying to execute this staetment, which is simpler and does the same thing

UPDATE wp_posts SET post_type = 'x' WHERE ID = (SELECT ID FROM wp_posts WHERE post_name = 'y' AND post_status = 'z' LIMIT 1)

but for some reason, it doesn't work.

This looks like you are executing a `SELECT` right after the `SET` query, in the same statement, with no `;` terminator. That's not supported. If this is in a MySQL client, separate them with `;`. If this is using a PHP interface (like in WP), multiple statements in one execution may not be supported at all, depending on the API. — Michael Berkowski, Mar 29 '17 at 13:18
@MichaelBerkowski Here is a simpler statement that should word, `UPDATE wp_posts SET post_type = 'x' WHERE ID = (SELECT ID FROM wp_posts WHERE post_name = 'y' AND post_status = 'z' LIMIT 1) ` do you see any issues with it? If I set `;` after set, then it will be considered two separate queries. — user7342807, Mar 29 '17 at 13:23
I would not expect that to produce the same error as your original attempt, but I would not be surprised if MySQL complained with a different error about not being able to specify a table for update that is used in a subquery. What error message specifically does that one fail with? — Michael Berkowski, Mar 29 '17 at 13:26
Yea, different error `Error Code: 1093 Table 'wp_posts' is specified twice, both as a target for 'UPDATE' and as a separate source for data ` this is all strange, the queries work independently but not together. — user7342807, Mar 29 '17 at 13:28
See http://stackoverflow.com/questions/45494/mysql-error-1093-cant-specify-target-table-for-update-in-from-clause You can use a join, or nest the subquery inside another subquery. — Michael Berkowski, Mar 29 '17 at 13:30

Trung Duong · Accepted Answer · 2017-03-29T13:54:07.997

1

You could try to UPDATE using JOIN

UPDATE wp_posts AS a 
       INNER JOIN wp_posts AS b ON a.id = b.id 
SET    a.post_type = 'x' 
WHERE  b.post_name = 'y' 
       AND b.post_status = 'z'

Or wrapping the update condition in one more select

UPDATE wp_posts 
SET    post_type = 'x' 
WHERE  id IN (SELECT id
              FROM   (SELECT id 
                      FROM   wp_posts 
                      WHERE  post_name = 'y' 
                             AND post_status = 'z') AS SOURCE)

edited Mar 29 '17 at 13:54

answered Mar 29 '17 at 13:30

Trung Duong

3,475
2
8
9

`Error Code: 1235 This version of MariaDB doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'` – user7342807 Mar 29 '17 at 13:33
Please remove LIMIT inside sub query. I've updated my answer – Trung Duong Mar 29 '17 at 13:34
I had done that before you asked, and I got this error without LIMIT 1 `Error Code: 1093 Table 'wp_posts' is specified twice, both as a target for 'UPDATE' and as a separate source for data` – user7342807 Mar 29 '17 at 13:36
Why you could not used directly WHERE statement, like *UPDATE wp_posts SET post_type = 'x' WHERE post_name = 'y' AND post_status = 'z'* – Trung Duong Mar 29 '17 at 13:38
Because I simplied the question just to ask it here, actually I am getting the ID from 1 table and trying to update another table with it. – user7342807 Mar 29 '17 at 13:41
I've updated my answer, please check. There is another method using JOIN, I will update later. – Trung Duong Mar 29 '17 at 13:45
Ah, for some reason it worked. it seems to simplify the question I showed example trying to get id and update 'the same table' but, when I mentioned two different tables, the query worked. (for anyone who comes here wandering this might be helpful) – user7342807 Mar 29 '17 at 13:55
please include that in your answer. The solutions won't work on the same table – user7342807 Mar 29 '17 at 13:56
@user7342807 I've updated my answer to work both on same and different table. – Trung Duong Mar 29 '17 at 13:58

How to assign a variable to select and update a field with it

EDIT/UPDATE:

1 Answers1