Selecting and updating SQL tables (in one hit)

Question

I've been always wondering how mutiple rows are selected from the database and one or more of their columns are updated on-the-fly. let's take a search engine for example, where thousands of results are grabbed and a view count is updated (using a single query).

Is that possible? do they use special SQL features in order to achieve that?

P.S.: If this question has been asked before please refer me cause I haven't found an identical one, and if it's lame, I know.

Search engines don't use SQL (in general); they use text-specific (and in some cases, highly customized) indexing engines that can do what you describe. — antlersoft, Sep 06 '12 at 14:34
@eggyal, I did not say it was 'wrong', I'm just trying to learn more about databases and 'stuff' around it. — Taher, Sep 06 '12 at 14:42
@t0xe, I'd like to update *selected* rows and not select *updated* rows. am I making sense? triggers do not work ON SELECT — Taher, Sep 06 '12 at 14:51
Yes. Misread your question. Upvoting eggyal's suggestion then. — jcd, Sep 06 '12 at 14:53

score 2 · Accepted Answer · edited May 23 '17 at 12:08

2

If you consider, for example, how Google does it, they use Bigtable (see this question for discussion) I don't know anything about how it works internally, so can't comment.

As for your question, it is not possible to do a select and an update in one go - in pure SQL, that is. It is, however, possible to do this with a stored function. Consider the following function (pseudo-code)

create function select_and_update(criteria) returns setof data_rows
  as
begin
    update some_table set view_count = view_count + 1 where criteria;
    return set "select * from some_table where criteria";
end;

Then in the application, you can make one database call:

SELECT * FROM select_and_update(criteria)

This way, there is only one "hit" from the application (i.e. you are only executing one query from the application against the database connection), however internally view count is updated and the data is returned.

Specifics, of course, will depend on the database engine and your particular needs, but I do use this technique in my web site quite extensively.

edited May 23 '17 at 12:08

Community

1
1

answered Sep 06 '12 at 14:38

Aleks G

52,841
25
149
233

1

That `return set "..."` syntax doesn't look like valid MySQL (with which this question is tagged). Also, such an approach is almost certainly not concurrency-safe in any RDBMS -- one ought to use a transaction if atomicity is required. – eggyal Sep 06 '12 at 14:45
niec1 on the Bigtable, thanks. – Taher Sep 06 '12 at 14:47
@Eggyal Please note that I said "pseudo-code" - of course it's not MYSQL. Naturally, in production I use transactions and proper sql (by the way, my production DB is not mysql). For example, in postgres plpgsql, you can use syntax `return query select * from some_table;` in a stored function. – Aleks G Sep 06 '12 at 14:48
@eggyal Also note that you tagged you question 'database' and 'sql'. Although 'mysql' tag is also present, the question is by far not 'mysql' specific. – Aleks G Sep 06 '12 at 14:56

score 1 · Answer 2 · answered Sep 06 '12 at 14:42

Assuming that the data is stored in a relational database, the answer is that the retrieval is not via a single select. I can think of two methods.

TYhe first is doing the retrieval through a stored procedure or table-valued user-defined function. The code would fetch the results and do the update and then return the results.

Alternatively, the retrieval can use cursors. One type of cursor is an updatable cursor, which allows the caller to read and update data in one call. Updatable cursors are generally rather slow, so they are not used much.

score 0 · Answer 3 · answered Sep 06 '12 at 14:35

0

If I understand your question correctly, why not join the two tables? something like this,

UPDATE table1 t1 
        INNER JOIN 
        (
            SELECT id, count(*) totalCount
            FROM tableName
            GROUP BY id
        ) t2 
            ON t1.id = t2.id
SET t1.col1 = t2.totalCount

answered Sep 06 '12 at 14:35

John Woo

238,432
61
456
464

I don't think it will achieve what the OP is after. He wants to run an update and return a set of rows - all in one database call. – Aleks G Sep 06 '12 at 14:38
@AleksG maybe, but i was thinking of this `selected from the database and one or more of their columns are updated on-the-fly.` – John Woo Sep 06 '12 at 14:40
I guess you could interpret it your way as well. Fair enough. – Aleks G Sep 06 '12 at 14:41
@JohnWoo, I amconcerned about selecting and updating values from *one* table. a join between a table and itself might work, but still the data returned, i'd like it to be coming from the select statement. – Taher Sep 06 '12 at 14:46

Selecting and updating SQL tables (in one hit)

3 Answers3