Hello,

I wonder if I can update the last record of a set of records in 1 query. Something like:

UPDATE Records SET FieldName=$Value WHERE/HAVING LAST

It only updates the last record of the Records table, setting its FieldName value to $Value.

Actually I have to get the last record id and update it, maybe there's somekind of magic MySQL statement to do it.

Thanks,
JM

    What do you mean by last?

    There is no concept of last in a relational database. All posts are identified by values in the columns only.

      hi,

      it depends of what you do with the table. for example for a News table, the last item is the newest news item... last id (primary key, MAX), date...

      JM

        As Mysql does not support scalar subqueries, you need to do it in two steps.

        // get id for record with highest  date 
$q = "select id from news order by dateColumn desc limit 1";
$c = mysql_query(q);
$r = mysql_fetch_array($c);

$id = $r[0];

$q ="update news set c = 'x' where id = ". $id;
mysql_query($q);
//add error handling

          hi,

          I am sure PostGre support "scalar subqueries". Anyway I don't think it's that bad, using 2 queries instead of one. It's easier to read, I am not into complex queries, it's like using 1 line of code to call 4 functions...

          so let's SELECT and UPDATE, it works just fine.
          thanks!
          JM

            10 days later

            You certainly can use a subquery in PostgreSQL. This is vastly preferred over using two queries (imagine if someone insterted a new max value between your SELECT and your UPDATE!!!!)

            This is the query:

            update news set c = 'x' where id = (select id from news order by dateColumn desc limit 1);

            If you really want to do two queries, then you must do it in a transaction. Even then you probably need to do some extra locking to prevent new max values being inserted.

            Cheers,

            Chris

              I read a few things about transactions and InnoDB tables, MySQL only supports transactions for InnoDB tables 🙁. Thanks for pointing that problem out because I forgot someone could insert a new entry. The solution would be to LOCK the table or INSERT DELAYED, I will read the manual because I never really tried these things.

              Thanks,
              JM

                Write a Reply...