Update Table with a Subquery Which Is Returning More Than One Row

Subquery returns more than 1 row with update

Use the LIMIT clause. This will limit the amount of results to one row.

UPDATE positions 
SET client_id =
    (
     SELECT clients.id 
     FROM clients 
     WHERE clients.file_name = positions.file_name
     LIMIT 1
    )

Keep in mind that the best practice is typically NOT to use subqueries to achieve what you want in SQL.

Subquery returns more than 1 row. Using SQL select to update a different tables results

In MySQL, a value of zero appearing in a WHERE clause means false.

So, UPDATE something SET col=val WHERE (SELECT colx FROM sometable) has the potential to be a valid query. If the inner SELECT gets just one row, and its colx column has the value 0, the update won't do anything. If the colx column has a nonzero value the query means UPDATE something SET col=val WHERE true. Accordingly, every row in sometable will be updated. I doubt that's what you want.

If the inner SELECT happens to return more than one row, the query isn't valid. You'll get the error 1242 you actually received.

(This business of interpreting numbers as Boolean values causes MySQL to accept some otherwise dodgy query syntax, like the syntax in your question.)

I guess you want to retrieve the job_queue_id values for the row or rows you actually want to update. So try something like this.

update jobqueue
   set jobqueue.`ignore`= '1'
 where jobqueue.job_queue_id IN (SELECT JobQueue_job_queue_id
                                   FROM mdtinfo
                                  WHERE product_name  = 'Example')

I guessed you have a column jobqueue.job_queue_id. You didn't tell us what columns you have in jobqueue.

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 ....

Subquery returns more than 1 row while update

You should try LIMIT like this...

$qry = mysql_query("update product set cat_tree =(select cat_name from cat_details where product.category = cat_details.cat_id LIMIT 1)");

UPDATE MySQL query error Subquery returns more than 1 row

I assume the table also has a product_id column that is unique for a product.

You need to tell the database to pick the english name for the same product.

UPDATE ps_product_lang
SET name=(select name from ps_product_lang_backup 
           where id_lang=2 
             and ps_product_lang.product_id = ps_product_lang_backup.product_id)
WHERE id_lang = 3

update sql getting select more than one row

I'm pretty sure you want a correlated subquery for the set query:

UPDATE table1 a
    SET (a.col1, a.col2, a.col3) = (
        SELECT coalesce(b.col1, 'N'), coalesce(b.col2, 'N'), coalesce(b.col3, 'N')
        FROM table2 b
        WHERE b.key1 = a.key1 AND
              b.key2 = a.key2 AND
              b.key3 = a.key3
       )
    WHERE . . .;

EDIT:

If you want to ensure that the subquery in the SET returns exactly one row and the columns are never NULL, then use aggregation:

UPDATE table1 a
    SET (a.col1, a.col2, a.col3) = (
        SELECT coalesce(MAX(b.col1), 'N'), coalesce(MAX(b.col2), 'N'), coalesce(MAX(b.col3), 'N')
        FROM table2 b
        WHERE b.key1 = a.key1 AND
              b.key2 = a.key2 AND
              b.key3 = a.key3
       )
    WHERE . . .;

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
)

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