Mysql help updating rows with same value

Question

I have a table named status_t:

**id** **page** **status** **locked**_by
  1      0        2          0
  1      1        2          0
  1      2        2          0
  2      0        0          0
  2      1        2          0
  2      2        2          0

primary key is ( id, page) .

In the above example I would like to update all the rows that all pages have status = 2.

i.e. the query should update all the rows with id = 1 to status 3. So the table will become

**id** **page** **status** **locked**_by
  1      0        3          1
  1      1        3          1
  1      2        3          1
  2      0        0          0
  2      1        2          0
  2      2        2          0

I have tried:

SELECT * FROM  status_t AS t
 WHERE id IN   
 (SELECT id FROM status WHERE status = 0) LIMIT 10

the above query fetches the rows to be updated but I cannot do that:

UPDATE status_t as S1 WHERE id IN 
(SELECT id FROM status_t WHERE status = 2) 
SET S1.status = 3, S1.locked_by = 1

EDIT:

THE ABOVE TABLE IS JUST AN EXAMPLE.

I do not want to update WHERE id = 1 . I just want to update rows no matter the id that have status = 2 for the same id. In the above example if the row with key (2, 2) had status = 2 then it should be updated.

Check my answer this will update table as you want

Saharsh Shah
– Saharsh Shah

2012-11-29 11:03:24 +00:00
Commented Nov 29, 2012 at 11:03 — Saharsh Shah
– Saharsh Shah, Commented Nov 29, 2012 at 11:03

Sergey Kalinichenko · Accepted Answer · 2012-11-29 11:05:02Z

1

This should work:

update status_t t
join (
    select distinct id from status_t s
    where status=2 and not exists (
        select 1 from status_t ss
        where s.id=ss.id and s.status <> ss.status
    )
) i on i.id = t.id
set t.status = 3

The inner query selects IDs where all values of status are set to 2. It achieves this result by checking that tere are no rows with the same ID and a different status.

Here is a demo on sqlfiddle.

answered Nov 29, 2012 at 11:05

Sergey Kalinichenko

729k85 gold badges1.2k silver badges1.6k bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Giorgos Komnino Over a year ago

thanks. How I can also apply a limit ? I would like to update at most N rows

Sergey Kalinichenko Over a year ago

@GiorgosKomnino What should happen to the rows beyond the limit?

Giorgos Komnino Over a year ago

The limit should be divided with 3 I think. So in the above example I can only put limit 3, 6, 9 etc. If I put limit 2 then it will update only the first two rows and then the last (id=1, page=2) will not be updated

Sergey Kalinichenko Over a year ago

@GiorgosKomnino Would the limit on the number of different IDs work then? If you set the limit to, say, 2, then up to six rows will be updated? If that would work for you, simply add limit 2 (3, 4, whatever) before the last closing parenthesis.

Saharsh Shah · Accepted Answer · 2012-11-29 11:22:59Z

1

Try this:

UPDATE status_t a 
INNER JOIN (SELECT id FROM status_t WHERE STATUS = 2 GROUP BY id HAVING COUNT(*) = 3 LIMIT 1) AS b ON a.id = b.id
SET a.status = 3, a.locked_by = 1;

This will update data as you want for all pages have status = 2

edited Nov 29, 2012 at 11:22

answered Nov 29, 2012 at 11:02

Saharsh Shah

29.1k9 gold badges53 silver badges85 bronze badges

1 Comment

Saharsh Shah Over a year ago

@GiorgosKomnino using my answer you can put limit serially and it updates records as you want

SchmitzIT · Accepted Answer · 2012-11-29 10:49:33Z

0

The following should do the trick:

UPDATE status_t 
SET status = 3, locked_by = 1 
WHERE status = 2

There should be no need to use a sub-query, as the WHERE should be able to be used directly in the UPDATE statement.

EDIT:

I just noticed that you only had an update against the rows with ID = 1, which does not match the statement 'In the above example I would like to update all the rows that all pages have status = 2.. The rows whereid = 2, andstatus = 2` would also be updated by my query.

If you need to only update a particular id, add the following at the end of my query:

AND id = 1

answered Nov 29, 2012 at 10:49

SchmitzIT

9,74912 gold badges71 silver badges96 bronze badges

Comments

SRIRAM · Accepted Answer · 2012-11-29 10:50:56Z

0

try this

  update status_t set staus=3 ,locked_by=1 
  where id=1

or

 update status_t set status=3 ,locked_by=1 
  where status=2

answered Nov 29, 2012 at 10:50

SRIRAM

1,8982 gold badges17 silver badges18 bronze badges

Comments

Ayyappan Sekar · Accepted Answer · 2012-11-29 10:52:56Z

0

UPDATE status_t SET status = 3, S1.locked_by = 1 WHERE id = 1

answered Nov 29, 2012 at 10:52

Ayyappan Sekar

11.5k2 gold badges22 silver badges22 bronze badges

Comments

Sashi Kant · Accepted Answer · 2012-11-29 10:57:39Z

0

Try this::

UPDATE status_t SET status = 3, locked_by = 1 WHERE status = 2

And if you want to achieve this in your way :

UPDATE 
status_t as S1 
INNER JOIN 
(
   SELECT keyword_id 
    FROM status_t WHERE status = 2
 ) as tempStatus ON id= tempStatus.keyword_id

SET S1.status = 3, S1.locked_by = 1

edited Nov 29, 2012 at 10:57

answered Nov 29, 2012 at 10:50

Sashi Kant

13.5k9 gold badges50 silver badges71 bronze badges

Collectives™ on Stack Overflow

Mysql help updating rows with same value

6 Answers 6

4 Comments

1 Comment

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

4 Comments

1 Comment

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related