Geek asks and answers

Can I update / select from a table in a single query?

I need to select data when viewing a page and update the “views” column, is there a way to do this in one request or do I need to use for different requests?

+2
source share
5 answers

If you do not want / do not want to use a transaction, you can create a stored procedure that first updates the view counter, then selects the values ​​and returns them to the user.

+2
source

You would need to do this in two operations in one transaction

Begin Tran

Update Pages Set Views = Views + 1 Where ID = @ID
Select Columns From Pages Where ID = @ID

Commit Tran
+2
source

, , SQL Server OUTPUT

USE AdventureWorks;
GO
DECLARE @MyTestVar table (
    OldScrapReasonID int NOT NULL, 
    NewScrapReasonID int NOT NULL, 
    WorkOrderID int NOT NULL,
    ProductID int NOT NULL,
    ProductName nvarchar(50)NOT NULL);

UPDATE Production.WorkOrder
SET ScrapReasonID = 4
OUTPUT DELETED.ScrapReasonID,
       INSERTED.ScrapReasonID, 
       INSERTED.WorkOrderID,
       INSERTED.ProductID,
       p.Name
    INTO @MyTestVar
FROM Production.WorkOrder AS wo
    INNER JOIN Production.Product AS p 
    ON wo.ProductID = p.ProductID 
    AND wo.ScrapReasonID= 16
    AND p.ProductID = 733;
SELECT OldScrapReasonID, NewScrapReasonID, WorkOrderID, 
    ProductID, ProductName 
FROM @MyTestVar;
GO
+1

PostgreSQL UPDATE RETURNING, , SELECT:

UPDATE mytable
 SET views = 5
 WHERE id = 16
 RETURNING id, views, othercolumn;

I am sure this is not a standard. I do not know if any other databases implement.

Edit: I just noticed that your question has a MySQL tag. Maybe you should mention this in the question itself. This is a good general database question, though - I would like to see how to do this in other databases.

0
source

I used this trick with Java and SQL Server, also you can send two commands in one PreparedStatement.

update tablex set y=z where a=b \r\n select a,b,y,z from tablex

It must be in a read transaction in order to work, as you think, it should, though.

0
source

More articles:


All Articles