0

I have a SELECT request - it works fine:

select meta_value
from wp_posts v
left join wp_postmeta pm on (pm.post_id = v.id)
left join wp_posts p on (v.post_parent = p.id)
where meta_key in ('_price','_regular_price')
and v.post_type = 'product_variation'
and p.id = '1743'
limit 0,100

Request find several post's meta_value relative to post with id = 1743.

It returns me 4 (four) needed fields with values like

400
500
300
350

I need an UPDATE request for it.

I try

Update wp_posts 
set meta_value = 1000
where id IN (select v.id
            from wp_posts v
            left join wp_postmeta pm on (pm.post_id = v.id)
            left join wp_posts p on (v.post_parent = p.id)
            where meta_key in ('_price','_regular_price')
                and v.post_type = 'product_variation'
                and p.id = '1743')

But it cause 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 WHERE meta_key in ( 'price' , '_regular_price' ) and v.post' at line 2

This function as a part of UPDATE

select v.id
from wp_posts v
left join wp_postmeta pm on (pm.post_id = v.id)
left join wp_posts p on (v.post_parent = p.id)
where meta_key in ('_price','_regular_price')
and v.post_type = 'product_variation'
and p.id = '1743'

Returns me right ID'S like

1744 1744 1745 1745

But UPDATE function with this select make an error.

There is mistake??? Help me please

closed as unclear what you're asking by ypercubeᵀᴹ, LowlyDBA, mustaccio, Max Vernon, McNets Jun 30 '17 at 6:33

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 5
    The error doesn't match the code. – ypercubeᵀᴹ Jun 29 '17 at 15:06
1

There's a bug or maybe lack of functionality in various versions of MariaDB/MySQL that prevents update of a table that's part of a sub-select. Small example:

create table A (x int not null, y int not null);
insert into A (x,y) values (1,1),(2,2),(3,3);
update A set y = 5 where x in ( 
    select x from A where y in (1,2) 
);
ERROR 1093 (HY000): Table 'A' is specified twice, both as a target for 'UPDATE' and as a separate source for data

You can fool the DBMS by hiding the table in a nested table

update A 
    set y = 5 
where x in (
    select x from ( 
        select x from A where y in (1,2) 
    ) as T 
);
Query OK, 2 rows affected (0.01 sec)

MySQL on the other hand supports a non standard update where you can update a join, but since you did not provide create table statements I'll leave that out of the answer (I'm too lazy to figure out what your tables look like :-)

Not the answer you're looking for? Browse other questions tagged or ask your own question.