CODEWITHSUNDEEP
mysqlsql-update
UnkwnTech
UnkwnTech

Reputation: 90871

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

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?

Upvotes: 2

Views: 2978

Answers (5)

Nathan Feger
Nathan Feger

Reputation: 19496

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.

Upvotes: 0

SQLMenace
SQLMenace

Reputation: 135011

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

Example

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

Upvotes: 1

Neall
Neall

Reputation: 27134

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

UPDATE mytable
 SET views = 5
 WHERE 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.

Upvotes: 0

Espo
Espo

Reputation: 41929

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.

Upvotes: 2

GateKiller
GateKiller

Reputation: 75869

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

Begin Tran

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

Commit Tran

Upvotes: 2

Related Questions