0

I have this query

SELECT *, min(id) 
FROM mytable 
WHERE col2='specval' 
GROUP BY col3

that selects rows I need.

I would like to set 

UPDATE mytable
set col4 = col3+1000

for the rows selected above respectively.

So, how to update that rows by setting col4 in that table as col4=col3+1000 only for each selected row respectively?

Thank you.

Haradzieniec
  • 9,086
  • 31
  • 117
  • 212
  • 2
    `select *, min(id)` doesn't select the rows with the minimum id in each group. It selects the minimum id, but the other columns come from random rows in the group, not the same row with the minimum. See http://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column?rq=1 for how to get the right rows. – Barmar Jan 01 '17 at 16:37

3 Answers3

1

you can use a query like this:

UPDATE mytable
set col4 = col3+1000
WHERE id in (
  SELECT * FROM (
    SELECT min(id) 
    FROM mytable 
    WHERE col2='specval' 
    GROUP BY col3
  ) as myids
);
Bernd Buffen
  • 14,525
  • 2
  • 24
  • 39
1

You can use UPDATE with JOIN:

UPDATE mytable t1
        INNER JOIN
    (SELECT 
        col3, MIN(id) id
    FROM
        mytable
    GROUP BY col3) t2 ON t1.col3 = t2.col3 AND t1.id = t2.id 
SET 
    t1.col4 = t1.col3 + 1000;
Gurwinder Singh
  • 38,557
  • 6
  • 51
  • 76
0

You can use WHERE in update clause too. So depending on what "selected" means to you ( I'm guessing from your SELECT ), you might do something like this:

UPDATE mytable
SET col4 = col3+1000
WHERE col2 = 'specval';
erik258
  • 14,701
  • 2
  • 25
  • 31