Update Table Column in Multiple Rows based on a Selection in SQL

Question

I have selected a group of rows using Select statement in SQL, and I'm trying to figure out how to update a column in this table to a value based on said selection.

The Selection:

SELECT user.userID FROM user WHERE user.status = '1' or user.status = '2' LIMIT 1000 OFFSET 50;

The Update:

UPDATE user SET user.status = '3';

The end result I am looking for is the status column of all the selected users be updated to '3'. I want to be able to do this in one SQL query and not have to loop or anything.

Thanks for any insight!

UPDATE:

Sorry! I forgot to add the offset. I want to select the rows after the first 50 returned rows. So I want all the rows after the first 50 that match the criteria, to have the status column changed to 3.

Yograj Sudewad · Accepted Answer · 2014-07-08T10:44:35.357

4

OLD

UPDATE user SET user.status = '3' WHERE user.status = '1' or user.status = '2';

Please try this query...

NEW

After Edit Question (As per offset)

try this below query for your offset.

UPDATE user SET user.status = '3' where  user.userID NOT IN(Select
userid from 
(SELECT userID as userid FROM user WHERE status = '1' or status = '2' LIMIT 50) 
as temptbl);

edited Jul 08 '14 at 10:44

answered Jul 08 '14 at 09:46

Yograj Sudewad

343
2
9

Alternatively `WHERE user.status IN ( '1' , '2' )` – Simon at The Access Group Jul 08 '14 at 09:47
2

@YograjSudewad Really? http://stackoverflow.com/questions/782915/mysql-or-vs-in-performance – yunzen Jul 08 '14 at 09:54

Raouf Athar · Answer 2 · 2014-07-08T10:38:34.113

1

UPDATE user SET user.status = '3' where user.status in ('1', '2');

Updated:

UPDATE user SET user.status = '3' where user.userID in (SELECT user.userID FROM user WHERE user.status = '1' or user.status = '2' LIMIT 1000 OFFSET 50);

edited Jul 08 '14 at 10:38

answered Jul 08 '14 at 09:47

Raouf Athar

1,803
2
16
30

score 0 · Answer 3 · answered Jul 08 '14 at 09:52

0

Please Try this

UPDATE user SET user.status = '3' WHERE user.status IN ( '1' , '2' )

answered Jul 08 '14 at 09:52

user3815782

1

yunzen · Answer 4 · 2014-07-08T13:39:25.387

0

new:

Use two subselects (Thanks to Yograj Sudewad for poiting me to my mistake)

UPDATE user 
SET user.status = '3'
WHERE user.userID IN (
    SELECT userId 
    FROM (
        SELECT user.userID 
        FROM user 
        WHERE user.status = '1' or user.status = '2' 
        LIMIT 1000 OFFSET 50
    ) AS tableWithNoName
)

old:

You could just use the WHERE part from your SELECT in your UPDATE as well. Like so

UPDATE user 
SET user.status = '3'
WHERE user.status = '1' 
    OR user.status = '2';

But if you want the UPDATE from the uid list, then:

<?php
// The uid list from the select may be 
$uid_list = array(1,4,7,12,401);
// then you could make
$sql = "
UPDATE user 
SET user.status = '3'
WHERE user.userID IN (" . implode(',', $uid_list) , ")

";

edited Jul 08 '14 at 13:39

answered Jul 08 '14 at 09:52

yunzen

32,854
11
73
106

@Guy Then use a subselect like [here](http://stackoverflow.com/questions/10728775/limit-offset-or-offset-in-an-update-sql-query) See my updated answer – yunzen Jul 08 '14 at 10:17
That doesn't seem to work. I fed it exactly that but it complained about the query and it failed. – Guy Jul 08 '14 at 10:36
What exactly does it complain about? – yunzen Jul 08 '14 at 10:55
@HerrSerker Mysql cant allow u to use same table in the inner query directly because you are performing update data. – Yograj Sudewad Jul 08 '14 at 10:59
MySql gives Error like:"You can't specify target table 'User' for update in FROM clause" – Yograj Sudewad Jul 08 '14 at 11:04
@YograjSudewad I see, you have to use a subselect in a subselect. – yunzen Jul 08 '14 at 11:05

Update Table Column in Multiple Rows based on a Selection in SQL

4 Answers4

new:

old: