Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How UPDATE and SELECT at the same time

I need to update some rows of the tables and then display these rows. Is there a way to do this with one single query and avoid this 2 query ? :

UPDATE table SET foo=1 WHERE boo=2

SELECT * from table WHERE ( foo=1 ) AND ( boo=2 )
like image 499
xRobot Avatar asked May 29 '10 08:05

xRobot


People also ask

Can we use UPDATE and SELECT together?

The subquery defines an internal query that can be used inside a SELECT, INSERT, UPDATE and DELETE statement. It is a straightforward method to update the existing table data from other tables. The above query uses a SELECT statement in the SET clause of the UPDATE statement.

How do I use UPDATE SELECT?

Example: SELECT FOR UPDATE in action A complete transaction that uses SELECT FOR UPDATE on that table could look like this: BEGIN; SELECT * FROM kv WHERE k = 1 FOR UPDATE; UPDATE kv SET v = v + 5 WHERE k = 1; COMMIT ; Working line by line through the statement above: The first line, BEGIN , initiates the transaction.

How do you UPDATE and in SQL?

UPDATE Syntax Notice the WHERE clause in the UPDATE statement. The WHERE clause specifies which record(s) that should be updated. If you omit the WHERE clause, all records in the table will be updated!


2 Answers

In PostgreSQL v8.2 and newer you can do this using RETURNING:

UPDATE table
SET foo=1
WHERE boo=2
RETURNING *
like image 135
Mark Byers Avatar answered Oct 05 '22 18:10

Mark Byers


You can use a stored procedure in PL/pgSQL. Take a look at the [docs][1]

Something like this

CREATE FUNCTION run(fooVal int, booVal int) 
RETURNS TABLE(fooVal int, booVal int)
AS $$
BEGIN
  UPDATE table SET foo = fooVal WHERE boo= booVal;
  RETURN QUERY SELECT fooVal, booVal from table WHERE ( foo = fooVal ) AND ( boo = booVal );
END;
$$ LANGUAGE plpgsql;

You will save the roundtrip time for sending another statement. This should not be a performance bottleneck. So short answer: Just use two queries. That's fine and this is how you do it in SQL.

[1]: http://www.postgresql.org/docs/8.4/static/plpgsql.html docs

like image 42
Janning Avatar answered Oct 05 '22 18:10

Janning