Search code examples
mysqlsqlreturn-valueblack-box

SQL returning values - how to do it?

We all know that in programming languages the return values are a most basic thing. But when it comes to databases, we just don't expect any return value. This is a wrong concept, since it reduces our database to a black-box. It may be sometimes good to know e.g. which rows are affected by an update.

So how can we build databases that return a value after inserts or updates? (Not after selects, since this always returns a value per se)

And can you give me some sample code for e.g. MySQL, like a list with the primary keys of the updated rows?

Solution

  • You might want to read this related SO topic (I've linked to the answer by @Erwin Brandstetter), quoted here:

    In PostgreSQL you could use the RETURNING clause.
    Oracle also has a RETURNING clause.
    SQL-Server has an OUTPUT clause.
    But MySQL doesn't have anything like that.