Updating Row with Subquery Returning Multiple Rows

Subquery returning multiple rows during update

The problem is that your subquery in the SET clause returns multiple rows.

Having a WHERE clause will only filter which records get updated and nothing else.

In addition, your where clause will either always return true or always return false.

You should look into how to properly do a correlated update:

https://stackoverflow.com/a/7031405/477563

How to update multiple rows on table with the result of a select subquery in Mysql

I don't know what your tables looks like but I'm just assuming that the product in your inner join product (given above) is your table2 with columns p.id_product and p.quantity and your product_quantity_cart has columns id_product, price_product, c.price, and c.id_shopping_cart

Then the query could be like this;

update product_quantity_cart c JOIN product p ON p.id_product = c.id_product
set c.price_product = c.price * p.quantity
WHERE c.id_shopping_cart=7

how to update multiple rows from subquery?

Your table 004 is not necessary into the subsellect. Try this:

update table_004 t4
set (t4.status, t4.bar_date) =
(
    select  99,  max(foo_date)
    from table_001 t1
    inner join table_002 t2 on  t1.keyA = t2.keyA
    inner join table_003 t3 on  t2.keyA = t3.keyA
    where t1.id in(1, 2, 3, 4, 5) and t4.keyB = t3.keyB
    group by t1.id
)
where exists
(
    select *
    from table_001 t1
    inner join table_002 t2 on  t1.keyA = t2.keyA
    inner join table_003 t3 on  t2.keyA = t3.keyA
    where t1.id in(1, 2, 3, 4, 5) and t4.keyB = t3.keyB
)

Subquery returns more than 1 row solution for update query using select statement

When you use update with SET configuration=(SELECT ...) the subquery has to return no more than one value (one row). If it returns more than one value how do you assign two rows table for example to scalar configuration field. So you should figure out WHY your subquery returns more than one row and fix the subquery or decide which ONE value to select for update in case of more than one row. For example you can select maximum value

SELECT MAX(ad_news_texte.headline)...

or any one first value

(SELECT ad_news_texte.headline)... LIMIT 1)

and so on...

If you need to concatenate all rows and put it into one row configureation you can use GROUP_CONCAT() mysql function:

SET configuration=(SELECT GROUP_CONCAT(DISTINCT ad_news_texte.headline) FROM ....

More than one row returned by a subquery used as an expression when UPDATE on multiple rows

I think I understand now... if I get your problem, you want to set the status to 'X' for the oldest five records and 'Y' for everything else?

In that case I think the row_number() analytic would work -- and it should do it in a single pass, two scans, and eliminating one order by. Let me know if something like this does what you seek.

with ranked as (
  select
    id, row_number() over (order by date_registered desc) as rn
  from people
)
update people p
set
  status = case when r.rn <= 5 then 'X' else 'Y' end
from ranked r
where
  p.id = r.id

Any time you do an update from another data set, it's helpful to have a where clause that defines the relationship between the two datasets (the non-ANSI join syntax). This makes it iron-clad what you are updating.

Also I believe this code is pretty readable so it will be easier to build on if you need to make tweaks.

Let me know if I missed the boat.

---

Related Topics