在一个查询中更新并选择 [英] Update and Select in one query

问题描述

你好朋友，
我一直在更新记录.有两个表
1. tbl_rate具有列[rateid(PK),clientid,rvID(FK)]
2. tbl_rateversion具有列[rvID(PK),clientid]

我想使用条件

Hello Friends,
I have stuck in updating records. There are two tables
1. tbl_rate with column [rateid(PK),clientid,rvID(FK)]
2. tbl_rateversion with column [rvID(PK),clientid]

I want to update rvID of tbl_rate using condition

tbl_rate.clientid = tbl_rateversion.clientid

更新tbl_rate的rvID
任何想法，我怎么能为此查询??

我试过了

Any idea, how can i write query for this??

I have tried

update tbl_rate set rvID = (select distinct tbl_rateversion.rvID from tbl_rateversion
inner join tbl_rate on tbl_rate.clientid = tbl_rateversion.clientid) where ??

但无法将其放在何处.

在此先感谢

But not able to put where condition for it.

Thanks in advance

推荐答案

您可以尝试以下方式:

You can try something like:

update tr
    SET
      rvID = trr.rvID
FROM tbl_rate tr
    JOIN tbl_rateversion trr ON trr.clientid = tr.clientid
WHERE  . . . .(add other condition) ...

如果使用的是SQL 2008，则可以使用Merge语句.示例:

http ://blog.sqlauthority.com/2008/08/28/sql-server-2008-introduction-to-merge-statement-one-statement-for-insert-update-delete/ [ ^ ]

If you are using SQL 2008, then you can use Merge statement. Example:

http://blog.sqlauthority.com/2008/08/28/sql-server-2008-introduction-to-merge-statement-one-statement-for-insert-update-delete/[^]

UPDATE tr
SET rvID = tresn.rvID
FROM tbl_rate tr
INNER JOIN tbl_rateversion tresn
ON tresn.clientid = tr.clientid
AND (Other Condition )
WHERE ?? 

有关此的更多信息，请参见下面的链接
[ ^ ]

More on this You can refer below link
[^]

这篇关于在一个查询中更新并选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！