Can I Update/select From A Table In One Query?

June 25, 2024 Post a Comment

I need to select data when a page is viewed and update the 'views' column is there a way to do this in one query, or do I have to use to distinct queries?

Solution 1:

If you do not want/need to use a transaction, you could create a stored procedure that first updates the view count and then selects the values and return them to the user.

Solution 2:

You would have to do this in two statements in one transaction

Begin Tran

Update Pages Set Views = Views +1Where ID =@IDSelect Columns From Pages Where ID =@IDCommit Tran

Solution 3:

It would help if you listed the RDBMS you are using SQL Server has the OUTPUT statement

Example

USE AdventureWorks;
GO
DECLARE@MyTestVartable (
    OldScrapReasonID intNOTNULL, 
    NewScrapReasonID intNOTNULL, 
    WorkOrderID intNOTNULL,
    ProductID intNOTNULL,
    ProductName nvarchar(50)NOTNULL);

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

Solution 4:

PostgreSQL's UPDATE statement has the RETURNING clause that will return a result set like a SELECT statement:

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

I'm pretty sure this is not standard though. I don't know if any other databases implement it.

Edit: I just noticed that your question has the "MySQL" tag. Maybe you should mention it in the question itself. It's a good generic database question though - I would like to see how to do it in other databases.

Solution 5:

I used this trick with Java and SQL Server will also let you send two commands in a single PreparedStatement.

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

This will need to be in a read committed transaction to work like you think it should though.

Lab Sql