当前位置：文江博客话题详情

我可以在一个查询中更新/选择表吗？

发布于 2024-07-04 22:17:13 字数 65 浏览 8 评论 0原文

我需要在查看页面时选择数据并更新“视图”列，有没有一种方法可以在一个查询中执行此操作，或者我是否必须使用不同的查询？

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

清泪尽 2024-07-11 22:17:13

我在 Java 和 SQL Server 中使用了这个技巧，它还允许您在单个PreparedStatement 中发送两个命令。

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

这需要在读提交事务中才能像您想象的那样工作。

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.

回复收藏 0 原文

爱她像谁 2024-07-11 22:17:13

如果您不想/不需要使用事务，则可以创建一个存储过程，该过程首先更新视图计数，然后选择值并将其返回给用户。

回复收藏 0 原文

被翻牌 2024-07-11 22:17:13

您必须在一个事务中的两个语句中执行此操作

Begin Tran

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

Commit Tran

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

回复收藏 0 原文

年少掌心 2024-07-11 22:17:13

如果您列出您正在使用的 RDBMS 将会有所帮助
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

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

回复收藏 0 原文

未央 2024-07-11 22:17:13

PostgreSQL 的 UPDATE 语句具有将返回结果的 RETURNING 子句像 SELECT 语句一样设置：

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

我很确定这不是标准的。不知道其他数据库有没有实现。

编辑：我刚刚注意到你的问题有“MySQL”标签。也许你应该在问题本身中提及它。不过，这是一个很好的通用数据库问题 - 我想看看如何在其他数据库中做到这一点。

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.

回复收藏 0 原文

~没有更多了~