UPDATE query from SELECT array in one statment

Question

Hi I have 2 SQL statements

Statement 1:

SELECT id, name, version FROM mydb WHERE device IS NULL AND Activated=1 ORDER BY id ASC LIMIT 10

Statement 2:

UPDATE mydb SET device='$device' WHERE name IN ('$itemsArray')

I have been getting the $itemsArray array from the first statement, but now I need to combine these statements, and I am not sure how to go about it and still keep it efficient.

When I have tried it myself I get 0 rows affected:

UPDATE mydb SET device='$device' WHERE Name IN (SELECT name FROM mydb WHERE device IS NULL AND Activated=1 ORDER BY id ASC LIMIT 10)

Edit: Removed quotes from subquery. Now I get this error: "This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'"

Using MySQL Version: 5.7.15

You need to take the quotes off your subquery - it's currently a string instead of a query. — , Dec 30 '16 at 01:05
since you tagged as php, you're most likely trying to run a multi-query and your method/server/api doesn't support it. There isn't enough php here or the api used to connect/query with btw. or what `$itemsArray` and `$device` are and its value(s). — Funk Forty Niner, Dec 30 '16 at 01:14
You don't need SELECT (really puzzled why no one mentioned this already - except I suppose you may need to rethink the limit). You just need JOIN — Strawberry, Dec 30 '16 at 01:18
@RickJames Personally, I thought the question was unclear in regards to `UPDATE mydb SET device='$device' WHERE name IN ('$itemsArray')` and the other query they wanted to run, and [a comment I left the OP](http://stackoverflow.com/questions/41389220/update-query-from-select-array-in-one-statment#comment69981029_41389220) but they never responded to it , which is why and as I stated below about my being the one who only posted the possible dupe as a comment, Maybe a multi query was another possible solution, who knows. I guess we'll never know until the OP does respond or if they will. — Funk Forty Niner, Dec 30 '16 at 02:09
I'm guessing this is some kind of queue, and he wants to do 10 at a time. And/or `name` is unique, so he gets 10 either way. — Rick James, Dec 30 '16 at 06:17

score 2 · Answer 1 · edited May 23 '17 at 10:30

2

In the MySQL documentation for 5.7 it states "MySQL does not support LIMIT in subqueries for certain subquery operators"

http://dev.mysql.com/doc/refman/5.7/en/subquery-restrictions.html

I also found this related post here on subqueries error with the LIMIT keyword: MySQL - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery

Hope this helps :)

edited May 23 '17 at 10:30

Community

1
1

answered Dec 30 '16 at 01:18

Richard McCarthy

81
4

Both links make reference to the use of Limit within a sub query and the second link mentions a solution to a similar problem using a Join which was a solution here, so I hope it would prove relevant. I was more focused on the subquery side and the use of a Limit keyword in MySql version 5.7 causing an error than the update. Hoping it helped anyway :) – Richard McCarthy Dec 30 '16 at 01:42
@RickJames Barmar closed it as a duplicate and you reopened. IMHO, if you have a problem with RIchard's answer, take it up with Barmar. Plus, he posted an answer on top of the dupe close. I hope you're not sore because your answer didn't get an upvote/accepted. – Funk Forty Niner Dec 30 '16 at 01:50
@RickJames It doesn't matter the type of query. Any query that tries to use `WHERE column IN (subquery)` can't use `LIMIT` in the subquery. And the solution is the same for any type of query: convert `WHERE-IN` to `JOIN`. – Barmar Dec 30 '16 at 01:54
In fact, almost all uses of `WHERE column IN (subquery)` work better if you write them as `JOIN`, because MySQL sometimes optimizes `WHERE-IN` poorly. – Barmar Dec 30 '16 at 01:56
@Barmar Do you feel that Rick shouldn't have reopened and should be reclosed? – Funk Forty Niner Dec 30 '16 at 01:57
The real question is "how can I update 10 rows"; we have been side tracked into non-working `in(subquery)`, etc. I'm trying to bring the focus back to that 'real' question. – Rick James Dec 30 '16 at 01:57
The Dup applied only to "Statement 2", not "Statement 1". Hence, I felt that it was not an "exact dup". – Rick James Dec 30 '16 at 01:58
1

@RickJames He doesn't want to update 10 rows. He wants to get the names from 10 rows, and update all the rows with those names. To do that, he needs a subquery with `LIMIT`, and then needs to know how to use that to find all the related rows. The linked question explains how to combine them. – Barmar Dec 30 '16 at 01:59
@RickJames His question is about how to combine statement 1 and statement 2 to get the actual result he wants. The dup applies to that combination. – Barmar Dec 30 '16 at 01:59
@Fred-ii- -- I am more interested in solving the OP's problem than in points/upvotes/etc. OK, I do get irritated when a question is closed as a Dup that is not really a Dup. – Rick James Dec 30 '16 at 02:00
@Barmar -- Oops, you are right. But I find that many questions are too busy stuck in a dead end. They need to back up a step in order to solve the "real" problem. – Rick James Dec 30 '16 at 02:02
@RickJames I think this question is falling under a delicate subject and I can see why you're questioning it and reopened it. I was the one who posted the Possible duplicate with the link because it was posted in the answer. I could have closed it in one go, but left it as a comment to see if it was worthy of a duplicate instead. Barmar saw it as being that, yet posted his answer before I posted the link. In turn for that, I upvoted this answer. – Funk Forty Niner Dec 30 '16 at 02:02
Anyway, folding the `SELECT` in could involve getting rid of the word `SELECT` -- as Gorden and I have shown. – Rick James Dec 30 '16 at 02:03

score 2 · Accepted Answer · answered Dec 30 '16 at 01:19

2

Use JOIN to relate the subquery to the table.

UPDATE mydb AS t1
JOIN (SELECT name 
      FROM mydb 
      WHERE device IS NULL AND Activated=1 
      ORDER BY id ASC 
      LIMIT 10) AS t2 ON t1.Name = t2.Name
SET device = '$device'

answered Dec 30 '16 at 01:19

Barmar

741,623
53
500
612

Is there a way to return those selected names from this statement without needing to do another select statement? – jLynx Dec 30 '16 at 01:48
No, there's no way to perform an UPDATE and SELECT at the same time. – Barmar Dec 30 '16 at 01:52
Mind going over comments under this answer http://stackoverflow.com/a/41389396/1415724 which I might add was used to close the question with (you), but then reopened by Rick James. I've a good mind to flag the question and possibly reclose. – Funk Forty Niner Dec 30 '16 at 01:52

score 0 · Answer 3 · answered Dec 30 '16 at 01:05

0

This is the correct syntax looks more like this:

UPDATE mydb
    SET device = '$device'
    WHERE Name IN (SELECT name FROM mydb WHERE device IS NULL AND Activated=1 ORDER BY id ASC LIMIT 10);

A subquery should not be surrounded by quotes, like a string.

However, I don't think this will work for two reasons: (1) the table is the same and (2) the limit.

So, this might do what you want:

UPDATE mydb
    SET device = '$device'
    WHERE device IS NULL AND Activated = 1
    ORDER BY id ASC
    LIMIT 10;

You might need a more complicated query if you really want to update based on name and not id.

answered Dec 30 '16 at 01:05

Gordon Linoff

1,242,037
58
646
786

Thanks for the answer, but I get this error: "This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'" when doing 1 like you said, but with number 2, its not updating all 10 items – jLynx Dec 30 '16 at 01:13
The 2nd query only updates the 1st 10 rows, not all the rows that have the same names as in the 1st 10 rows. – Barmar Dec 30 '16 at 01:21

score 0 · Answer 4 · answered Dec 30 '16 at 01:23

0

I suggest that you are working too hard. Get rid of the SELECT:

UPDATE mydb SET device='$device'
    WHERE device IS NULL AND Activated=1
    ORDER BY id ASC LIMIT 10;

answered Dec 30 '16 at 01:23

Rick James

135,179
13
127
222

Performance would benefit from `INDEX(device, Activated, id)`. – Rick James Dec 30 '16 at 01:24

UPDATE query from SELECT array in one statment

4 Answers4