CODEWITHSUNDEEP
mysqlsql
Michael Mahony
Michael Mahony

Reputation: 1350

MySQL update with IN clause and subquery

I want to run some updates on my MySQL database. Here is the query that I want to use:

UPDATE `wphh_wp_eStore_tbl` 
SET `wphh_wp_eStore_tbl`.description = '<div class="dwrp">
<h2>F. Locker $109.99</h2>
<h2>Nike Outlet $109.99</h2>
<h2>Ch. Sports $107.99</h2>
<h2>Hoopers Hookup $89.99</h2>
<h2 class="special">These prices as of 11/20/13</h2>'
WHERE `wphh_wp_eStore_tbl`.id in (
    SELECT `wphh_wp_eStore_tbl`.id FROM `wphh_wp_eStore_tbl` 
    INNER JOIN `wphh_wp_eStore_cat_prod_rel_tbl` 
        on `wphh_wp_eStore_cat_prod_rel_tbl`.prod_id = `wphh_wp_eStore_tbl`.id
    WHERE `wphh_wp_eStore_cat_prod_rel_tbl`.cat_id = 5
    )

This generates the following error:

#1093 - You can't specify target table 'wphh_wp_eStore_tbl' for update in FROM clause

Why? I know in MSSQL I can do this:

Update tableone 
set columnname = 'xxx' 
where id in (
    select id 
    from tableone 
    where category = 10)

and it works.

What am I missing?

Upvotes: 0

Views: 286

Answers (2)

sam yi
sam yi

Reputation: 4934

I'm not a MySQL guy but could you do this instead? I also like using table alias with these ugly names.

UPDATE `wphh_wp_eStore_tbl` e
inner join `wphh_wp_eStore_cat_prod_rel_tbl` ecpr
  on ecpr.prod_id = e.id
  and ecpr.cat_id = 5 
set e.description = 'Some Text To Update With'

In SQL Server the "from" and the join come after the SET but.. not the case in mySQL apparently.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270873

The simple solution is to enclose the in list in an additional level of nesting. MySQL will then materialize the data:

UPDATE `wphh_wp_eStore_tbl` 
SET `wphh_wp_eStore_tbl`.description = '<div class="dwrp">
<h2>F. Locker $109.99</h2>
<h2>Nike Outlet $109.99</h2>
<h2>Ch. Sports $107.99</h2>
<h2>Hoopers Hookup $89.99</h2>
<h2 class="special">These prices as of 11/20/13</h2>'
WHERE `wphh_wp_eStore_tbl`.id in (
    select * from (SELECT `wphh_wp_eStore_tbl`.id FROM `wphh_wp_eStore_tbl` 
INNER JOIN `wphh_wp_eStore_cat_prod_rel_tbl` on                 `wphh_wp_eStore_cat_prod_rel_tbl`.prod_id = `wphh_wp_eStore_tbl`.id
WHERE 
`wphh_wp_eStore_cat_prod_rel_tbl`.cat_id = 5) t
    )

Another way is to use join to with update.

Upvotes: 1

Related Questions