如何编写具有多个条件的更新查询。 [英] How do I write Update Query with Multiple Conditions.

问题描述

我有以下帐户表

I have following Account table

Acct_ID     AM_ID    DM_ID    Userfield
111          1          2         Null
123          3          4         Null
321          5          6         Null

等......



我想在DM_ID中更新 AM_ID ，在用户字段 DM_ID $ c>。

请让我知道我该怎么做。

这里 Acct_ID 是主键。

什么是可能的更新查询。

and so on....

I want to Update AM_ID in DM_ID and DM_ID in Userfield.
Please let me know how can i do it.
Here Acct_ID is primary Key.
What can be possible Update Query.

推荐答案

你的英文不清楚，但如果我知道你想设置DM_ID字段到AM_ID值和UserField到DM_ID值？如果是这样的话：

Your English isn't clear, but if I understand that you want to set the DM_ID field to the AM_ID value and the UserField to the DM_ID value? If so:

UPDATE Account
SET DM_ID = AM_ID, Userfield = DM_ID

这会更新所有行。如果要更新特定行：

This updates all rows. If you want to update a specific row:

UPDATE Account
SET DM_ID = AM_ID, Userfield = DM_ID
WHERE Acct_ID = SomeValue

我不确定你是否理解正确，但是..



以下查询替换 NULL 在 UserField 中，带有 DM_ID 值。

I'm not sure understand you correctly, but..

Below query replaces NULL in UserField with DM_ID values.

UPDATE t1 SET t1.Userfield = t2.DM_ID, t1.DM_ID = t2.AM_ID 
FROM dbo.Account AS t1 INNER JOIN Account AS t2 ON t1.Acct_ID = t2.Acct_ID

完整工作样本：

Fully working sample:

DECLARE @Account TABLE (Acct_ID INT, AM_ID INT, DM_ID INT, Userfield INT NULL)

INSERT INTO @Account (Acct_ID, AM_ID, DM_ID)
VALUES(111, 1, 2),
(123, 3, 4),
(321, 5, 6)

SELECT *
FROM @Account

UPDATE t1 SET t1.Userfield = t2.DM_ID, t1.DM_ID = t2.AM_ID
FROM @Account AS t1 INNER JOIN @Account AS t2 ON t1.Acct_ID = t2.Acct_ID

SELECT *
FROM @Account

结果：

1.查询

Results:
1. query

111 1   2   NULL
123 3   4   NULL
321 5   6   NULL

2.查询

2. query

111 1   1   2
123 3   3   4
321 5   5   6

这篇关于如何编写具有多个条件的更新查询。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！