SQL returning values - how to do it?

Question

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?

Most databases show the number of effected rows after inserts and updates so I am not sure what you are asking? — Byron, Mar 03 '13 at 19:53
Database is not a programming language, why does it have to return a value? — mvp, Mar 03 '13 at 19:53
@mvp: Are you asking: Why don't you use noSQL? Answer: I'll use noSQL in 5 years when the systems are really reliable...and will support SQL interfaces. — Marcus, Mar 03 '13 at 19:56
@Marcus: I am not asking anything, especially about NoSQL. I am simply saying that database is NOT a programming language, and your expectations that it can be coerced to behave exactly like one are simply wrong. — mvp, Mar 03 '13 at 20:11
Well, that's your opinion. But who is talking about "behaving exactly like one"?? — Marcus, Mar 03 '13 at 20:15
There is a great solution (also for MySQL): http://stackoverflow.com/questions/1388025/how-to-get-id-of-the-last-updated-row-in-mysql The trick is to put the value(s) in one or more variables and then use a select at the end of the insert/update to get the values. I use this, because I needed a solution where the returning of the values will be from the same thread doing the insert or update. But I'm not expecting any knowledge about multithreading or ACID from the people who closed this thread... — Marcus, Mar 13 '13 at 01:22

score 3 · Accepted Answer · edited May 23 '17 at 11:57

3

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.

edited May 23 '17 at 11:57

Community

1
1

answered Mar 03 '13 at 19:57

fenway

416
2
8

This looks like an answer at least. Thanks. – Marcus Mar 03 '13 at 20:23
I just found an even better solution here: http://stackoverflow.com/questions/1388025/how-to-get-id-of-the-last-updated-row-in-mysql. That hack works with mySQL, too. It's not a real return type, though, more like using a global variable, but in the end it will return the needed values. – Marcus Mar 03 '13 at 20:52

score 0 · Answer 2 · edited May 23 '17 at 11:49

0

A good way would be to SELECT the values before the update inside a transaction: https://stackoverflow.com/a/10663439/2115135 Just remember that update still can fail so take it in consideration before using the returned values.

edited May 23 '17 at 11:49

Community

1
1

answered Mar 03 '13 at 19:56

Jakub Kania

15,665
2
37
47

I can't execute 2 times every command. 1) This would affect the performance, and 2) still you're not 100% sure if the returned rows will also be the updated rows. – Marcus Mar 03 '13 at 20:09

score 0 · Answer 3 · edited May 23 '17 at 12:21

Depending on the method of execution used you usually can get the number of rows affected by any write operation performed on the my sql database. There are multiple ways, you can explicitly execute the statement as a batch with

SELECT ROW_COUNT()

at the end or you can use the features provided by the connection library to get the number of rows affected in the last set

For example : in PHP you can use

mysqli::$affected_rows

for getting the affected rows.

Edit : Is there any particular scenario, you would like more examples on?

Update : To answer the second part of the question.

Well, there is absolutely no way the you can get the primary keys of the affected rows in the same statement that I know. You will need to execute a statement batch as mentioned in the answer examples given by the other user.

START TRANSACTION; SELECT primary_key_columns FROM my_table WHERE status = 'O' FOR UPDATE; UPDATE my_table SET status = 'E' WHERE status = 'O'; COMMIT; More detail here : [https://stackoverflow.com/questions/10663338/i-need-primary-keys-of-the-affected-rows-to-be-returned-after-updating-a-table-i][1]

EDIT Modified the answer based on info given by Marcus in the comments below.

To retrieve the ID of every row affected by an update statement:

SET @uids := null;
UPDATE footable
   SET foo = 'bar'
 WHERE fooid > 5
   AND ( SELECT @uids := CONCAT_WS(',', fooid, @uids) );
SELECT @uids;

This will return a string with all the IDs concatenated by a colon. Source :How to get ID of the last updated row in MySQL?

The other option as mentioned by the answer given by @fenway is the output clauses for supported database.

Note that even in that case you essentially have to fill in the values before you send the data back following the same step mentioned above.

Now, I am sure, you are wondering why is it that the database do not return primary key values automatically. Well it has to do with the fact that

1) A DB does not insist on having a Primary key for each and every table. Its actually a UNIQUE constraint defined by us.

2) Not all scenarios do you need to have the affected primary keys returned . In most of the usual use-cases, after update you require at most the rows affected.

Remember, Relational DB programming requires a slightly different mindset from the usual problem set programming.

Atleast these are my two cents.

Regards

Shreyas N

I can't execute 2 times every command. 1) This would affect the performance, and 2) still you're not 100% sure if the returned rows will also be the updated rows. — Marcus, Mar 03 '13 at 20:08
You are **not** executing twice in every command. This is executed as single batch ( first method) and involves no round trip between the DB and the app. It is a single call so performance penalty is very very low. — JustShrey, Mar 03 '13 at 20:32
However, it does not answer my question. I need e.g. the primary keys of the changed rows. That's far behind `mysqli::$affected_rows`. — Marcus, Mar 03 '13 at 20:43
But it's possible, here's a solution: http://stackoverflow.com/questions/1388025/how-to-get-id-of-the-last-updated-row-in-mysql — Marcus, Mar 03 '13 at 21:04
Thank you. The multiple Id trick i had not known. It is definately better than the transaction select used above. Please note though,in both cases a select is still required to actually get the ids — JustShrey, Mar 03 '13 at 21:09

GreenOsmosis · Answer 4 · 2013-03-11T19:12:03.540

0

One way to accomplish this is to use a select statement that has the same filter as the update statement. Make sure however, that both queries run inside the same transaction so that the data doesn't change between each call.

{

update Customers set CITY = 'CHICAGO'

where ContactFName = 'Dan'

//execute query

select CustomerID from Customers

where ContactName = 'Dan'

//execute reader
//while the reader returns a row, set that return value to an element in a list 

//commit transaction

}//Transaction

edited Mar 11 '13 at 19:12

answered Mar 03 '13 at 20:27

GreenOsmosis

36
2

Before doing this, you need to call `LOCK TABLES Customers;`, and that's exactly what I don't want due to performance reasons. – Marcus Mar 03 '13 at 20:46

SQL returning values - how to do it?

4 Answers4

Linked