Updating a value from returned rows from SELECT statement
Updating a value from returned rows from SELECT statement
Using a single table USERS, I would like to update the top 20 values (based on marks column) of column STATUS which is currently NULL to "Waiting".
USERS
marks
STATUS
Is this possible using an Update (only to affect the top 20 users and the rest should be made to "N/A")?
Update
Right now I can only think of
Update
STATUS
from
USERS
where
STATUS is NULL
group by
MARKS desc
LIMIT
20;
But I get an error
EDIT:-
UPDATE
student
SET
status = 'Waiting'
where
branch in (
select
branch
from
(
select
branch
from
student
where
STATUS is NULL
order by
CGPA DESC
limit
1, 2
) temp
)
I've tried this, but it updates all the rows not just the two that I want (the top 2 sorted on marks)
what is the error reported?
– swayamraina
Sep 14 '18 at 17:15
Learn the syntax of
UPDATE. It's not even a valid syntax.– Eric
Sep 14 '18 at 17:46
UPDATE
Learn how to ask question here. stackoverflow.com/help/how-to-ask
– Eric
Sep 14 '18 at 17:46
@MadhurBhaiya Primary key isnt particularly important in this query, I edited my post with another query that I tried that did run but gave me a result such that all rows were changed instead of the top two like I wanted I had a look at the syntax and a few other topics and edited my question, apologies as I am new to this forum
– Shaun Fernandez
Sep 14 '18 at 18:38
2 Answers
2
Try this one.
UPDATE USERS SET status = 'Waiting' where ID in (select ID from USERS where STATUS is NULL order by MARKS DESC limit 1,20)
It says that this version of MySQL doesnt allow for limit inside such statements.I edited my post with something that did work but is giving me incorrect results
– Shaun Fernandez
Sep 14 '18 at 18:36
You can't use LIMIT in an "IN" subquery, but you can in a derived table, like UPDATE t1 JOIN (SELECT foo FROM t2 LIMIT 20) dt ON t1.id = dt.id SET t1.bar = 'bat';
– Scott Noyes
Sep 14 '18 at 18:47
Thanks @Scott Noyes for correcting the query.
– ABHI
Sep 15 '18 at 19:23
http://sqlfiddle.com/#!9/a850e/1
UPDATE users u
JOIN (
SELECT id
FROM users
WHERE `status` IS NULL
ORDER BY marks desc
LIMIT 20
) j
ON u.id = j.id
SET `status`='WAITING';
Thanks for contributing an answer to Stack Overflow!
But avoid …
To learn more, see our tips on writing great answers.
Required, but never shown
Required, but never shown
By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.
Please give table structure, especially primary key field details. It will help in formulating the query
– Madhur Bhaiya
Sep 14 '18 at 17:11