Basic SQL Update statement on MAX

Question

I have a table 'dummy' like this

name, status , date, is-updated
n1, s1, 2013-09-01, false
n2, s2, 2013-09-01, false
n1, s11, 2013-09-02, false

Now in-order to get the most recent timestamps for a particular name, i am using the query:

select name, status, max(date)
from dummy
where is-updated=false
group by name,status

This is working fine as expected, returning two rows

n2, s2, 2013-09-01, false
n1, s11, 2013-09-02, false

Now how to update the 'is-updated' value as 'true' for the above two rows ?? 'IN' clause in sql doesn't support multiple columns.

update dummy
set is-updated = true
where name, status, date in (

select name, status, max(date)
from dummy
where is-updated=false
group by name,status

)

Can anyone help with this ? Thanks.

Review [this question](http://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-backticks). `is-updated` must be backtick-quoted to use as a column name since it contains a `-` which means "minus". — Michael Berkowski, Sep 06 '13 at 18:36
your query that worlds... doesn't match the dataset with the two rows returned..? what is they key field for `dummy`? — gloomy.penguin, Sep 06 '13 at 18:39

gloomy.penguin · Answer 1 · 2013-09-06T19:29:12.627

0

Give me some more info and I'll update this to be better:

update dummy as d 
     join ( 
       select name, max(modifieddate) as max_mod
       from dummy 
       where is_updated = FALSE 
       group by name ) as max_mod_tbl
      on max_mod_tbl.name = d.name and 
         max_mod_tbl.max_mod = d.modifieddate and 

set is_updated = true 

where d.is_updated = false

http://www.sqlfiddle.com/#!2/26bad/1/0

i added rows_you_want_to_change.is-updated = d.is-updated because... in case you have two rows with elect name, status, max(date) as dt and different is-updated statuses

like key fields. and that query doesn't match the rows you say it returns..?

edited Sep 06 '13 at 19:29

answered Sep 06 '13 at 18:40

gloomy.penguin

5,833
6
33
59

Thanks for the reply. Yes. My first query is not returning what i expected :-( My intention was to get the latest update on the row where i am storing the date as YYYY-MM-DD format. select name, status, max(date) from dummy where is-updated=false group by name,status Here the answer is not the following :-( n2, s2, 2013-09-01, false n1, s11, 2013-09-02, false – user2755391 Sep 06 '13 at 18:49
@user2755391 if one of the answers here doesn't solve it for you, can you make an [sqlfiddle](http://www.sqlfiddle.com) for us? – gloomy.penguin Sep 06 '13 at 18:51
Here is the SQL Fiddle http://www.sqlfiddle.com/#!2/805ad/2 The row with ('a', '2013-09-05', 's2', FALSE) should be returned instead of ('a', '2013-09-04', 's1', FALSE). How to get that ? – user2755391 Sep 06 '13 at 19:09
@user2755391 you can't include `status` then. are these the rows you're looking for? `select name, max(modifieddate) from dummy where is_updated = FALSE group by name` – gloomy.penguin Sep 06 '13 at 19:23
But i want status also. This will work. SELECT name, status, modifieddate from dummy as dd where modifieddate in ( Select max(modifieddate) from dummy as d where d.name = dd.name and dd.is_updated = FALSE) But thinking can't we do without join. – user2755391 Sep 06 '13 at 19:28

score 0 · Answer 2 · answered Sep 06 '13 at 18:45

0

You can do this using update and join. The proper syntax is:

update dummy join
       (select name, status, max(date) as maxdate
        from dummy
        where `is-updated` = false
        group by name, status
       ) toupdate
       on dummy.name = toupdate.name and
          dummy.status = toupdate.status and
          dummy.date = toupdate.maxdate
    set `is-updated` = true;

The join does the filtering so you don't need a where clause.

answered Sep 06 '13 at 18:45

Gordon Linoff

1,242,037
58
646
786

Thank you for the reply. Here is the SQL Fiddle sqlfiddle.com/#!2/805ad/2 The row with ('a', '2013-09-05', 's2', FALSE) should be returned instead of ('a', '2013-09-04', 's1', FALSE). How to get that ? – user2755391 Sep 06 '13 at 19:12
@user2755391 . . . I think you just need to group by `name, status` rather than `name`. – Gordon Linoff Sep 06 '13 at 19:24
But that wont work. As it will give both the rows. " SELECT name, status, modifieddate from dummy as dd where modifieddate in ( Select max(modifieddate) from dummy as d where d.name = dd.name and dd.is_updated = FALSE) ". But i want without join. – user2755391 Sep 06 '13 at 19:31

Basic SQL Update statement on MAX

2 Answers2