更新同一张表中的行

huangapple
go评论102阅读模式
英文:

Update row in the same table

问题

我有一个表格1，看起来像这样：

  1. EID      Hold        Source
  2. 3232     KLME         jame.k@gmail.com
  3.          KLME         https://google.com
  4.          EEME         david.e@gmail.com
  5.          JJIN         Test@gmail.com
  6. 7232     JJIN         https://google.com

我只是想知道如何使用第一行来更新第二行的“Hold”为“KLME”。

例如，像这样：

  1. EID      Hold        Source
  2. 3232     KLME         jame.k@gmail.com
  3. 3232     KLME         https://google.com
  4.          EEME         david.e@gmail.com
  5. 7232     JJIN         Test@gmail.com
  6. 7232     JJIN         https://google.com

我有这个简单的脚本，但出现了一个问题，我得到了不同用户的不同EID，而不是正确的EID。

英文:

I have Table 1 that look something like this

  1. EID      Hold        Source
  2. 3232     KLME         jame.k@gmail.com
  3.          KLME         https://google.com
  4.          EEME         david.e@gmail.com
  5.          JJIN         Test@gmail.com
  6. 7232     JJIN         https://google.com

I'm just wondering how can I update the second row of hold "KLME" using the first row.

example like this

  1. EID      Hold        Source
  2. 3232     KLME         jame.k@gmail.com
  3. 3232     KLME         https://google.com
  4.          EEME         david.e@gmail.com
  5. 7232     JJIN         Test@gmail.com
  6. 7232     JJIN         https://google.com
  1. UPDATE Test_Table 
  2. SET EID = (
  3.     SELECT TOP 1 Test_Table.EID
  4.     FROM Test_Table 
  5.     WHERE Test_Table.Hold = Test_Table.Hold
  6.     AND Test_Table.EID <> '' OR Test_Table.EID <> NULL
  7. )

I have this simple script but I'm getting a different EID from different user instead of the correct EID for some reason.

答案1

得分: 2

使用 MAX()：

  1. UPDATE Test_Table 
  2. SET EID = (
  3.     SELECT MAX(EID)
  4.     FROM Test_Table a
  5.     WHERE a.Hold = Test_Table.Hold
  6. )

请注意，您无需过滤空白或空值，因为 MAX 会忽略空值，而任何非空白值都大于空白。

另外，通过不添加 WHERE 子句到 UPDATE，您将自动纠正对于特定 Hold 而言 EID 不同的任何不一致情况。

英文:

Use MAX():

  1. UPDATE Test_Table 
  2. SET EID = (
  3.     SELECT MAX(EID)
  4.     FROM Test_Table a
  5.     WHERE a.Hold = Test_Table.Hold
  6. )

Note how you don't need to filter out blanks or nulls because MAX ignores nulls and any non-blank value is greater than blank.

Also, by not adding a WHERE clause to the UPDATE, you'll automatically correct any inconsistencies with EID being different for a given Hold.

答案2

得分: 2

您可以使用可更新的公共表达式（CTE）和窗口函数。 这样做的好处是表只被扫描一次。

  1. WITH cte AS (
  2.     SELECT *,
  3.       OtherEID = MIN(NULLIF(tt.EID, '')) OVER (PARTITION BY tt.HOLD)
  4.     FROM Test_Table tt
  5. )
  6. UPDATE cte
  7. SET EID = OtherEID
  8. WHERE (EID IS NULL OR EID = '')
  9.   AND OtherEID IS NOT NULL;
英文:

You can use an updatable CTE and a window function. The benefit of this is that the table is only scanned once.

  1. WITH cte AS (
  2.     SELECT *,
  3.       OtherEID = MIN(NULLIF(tt.EID, '')) OVER (PARTITION BY tt.HOLD)
  4.     FROM Test_Table tt
  5. )
  6. UPDATE cte
  7. SET EID = OtherEID
  8. WHERE (EID IS NULL OR EID = '')
  9.   AND OtherEID IS NOT NULL;

答案3

得分: 1

有很多问题出现在你的查询中。

  1. 你不能对null使用等于（或不等于）检查，必须使用 IS NOT NULL。
  2. 你需要在你的 OR 条件周围加上括号，以获得正确的逻辑。
  3. 你实际上只需要更新缺少 EID 的行。

一个可能的解决方案，可以正确地更新行，并正确关联新值是

  1. UPDATE t1 SET
  2.   EID = (SELECT TOP 1 Test_Table.EID FROM Test_Table t2 WHERE t2.[Hold] = t1.[Hold] AND COALESCE(t2.EID, '') <> '')
  3. FROM Test_Table t1
  4. WHERE Test_Table.EID = '' OR Test_Table.EID IS NULL;
英文:

There is a lot going wrong with your query.

  1. You cannot use an equality (or inequality) check against null, you must use IS NOT NULL
  2. You need brackets around your ORed conditions to get the correct logic
  3. You really only need to update rows with a missing EID.

A possible solution which updates the correct rows, and correlates the new value correctly is

  1. UPDATE t1 SET
  2.   EID = (SELECT TOP 1 Test_Table.EID FROM Test_Table t2 WHERE t2.[Hold] = t1.[Hold] AND COALESCE(t2.EID,&#39;&#39;) &lt;&gt; &#39;&#39;)
  3. FROM Test_Table t1
  4. WHERE Test_Table.EID = &#39;&#39; OR Test_Table.EID IS NULL;

答案4

得分: 1

你可以使用group by和max()来实现，步骤如下：

首先按Hold获取最大的Eid：

  1. select Hold, max(EID) as Eid
  2. from Test_Table
  3. group by Hold

然后在将其与你的表连接后进行更新：

  1. update t
  2. set Eid = S.Eid
  3. from Test_Table t
  4. inner join (
  5.   select Hold, max(EID) as Eid
  6.   from Test_Table
  7.   group by Hold
  8. ) s on s.Hold = t.Hold
英文:

You can do it using group by and max() as follows :

First get the max(Eid) per Hold :

  1. select Hold, max(EID) as Eid
  2. from Test_Table
  3. group by Hold

Then apply the update after joining it to your table:

  1. update t
  2. set Eid = S.Eid
  3. from Test_Table t
  4. inner join (
  5.   select Hold, max(EID) as Eid
  6.   from Test_Table
  7.   group by Hold
  8. ) s on s.Hold = t.Hold

demo here

答案5

得分: 1

以下是您的数据的翻译部分：

  1. 创建表格 mytable(
  2.    EID    整数  
  3.   ,Hold   变量字符(20) 非空
  4.   ,Source 变量字符(100) 非空
  5. );
  6. 向 mytable 插入数据(EID,Hold,Source) 值 
  7. (3232,'KLME','jame.k@gmail.com'),
  8. (NULL,'KLME','https://google.com'),
  9. (NULL,'EEME','david.e@gmail.com'),
  10. (NULL,'JJIN','Test@gmail.com'),
  11. (7232,'JJIN','https://google.com');

使用以下查询：

  1. 更新 m1
  2. 设置 m1.EID=如果(m1.EID 是空的, m2.EID, m1.EID)
  3. 从 mytable m1
  4. 加入 mytable m2
  5. 在 m1.Hold=m2.Hold 和 m1.Source<>m2.Source 的条件下

dbfiddle

或者更简单的方式：

  1. 更新 m1
  2. 设置 m1.EID=m2.EID
  3. 从 mytable m1
  4. 加入 mytable m2
  5. 在 m1.Hold=m2.Hold 和 m1.Source<>m2.Source 和 m1.EID 是空的条件下

dbfiddle

英文:

your data

  1. CREATE TABLE mytable(
  2.    EID    INTEGER  
  3.   ,Hold   VARCHAR(20) NOT NULL
  4.   ,Source VARCHAR(100) NOT NULL
  5. );
  6. INSERT INTO mytable(EID,Hold,Source) VALUES 
  7. (3232,&#39;KLME&#39;,&#39;jame.k@gmail.com&#39;),
  8. (NULL,&#39;KLME&#39;,&#39;https://google.com&#39;),
  9. (NULL,&#39;EEME&#39;,&#39;david.e@gmail.com&#39;),
  10. (NULL,&#39;JJIN&#39;,&#39;Test@gmail.com&#39;),
  11. (7232,&#39;JJIN&#39;,&#39;https://google.com&#39;);

use following query

  1. update m1
  2. set m1.EID=iif(m1.EID is null,m2.EID,m1.EID)
  3. from mytable m1
  4. join mytable m2
  5. on m1.Hold=m2.Hold and m1.Source&lt;&gt;m2.Source

dbfiddle

or more simply

  1. update m1
  2. set m1.EID=m2.EID
  3. from mytable m1
  4. join mytable m2
  5. on m1.Hold=m2.Hold and m1.Source&lt;&gt;m2.Source and m1.EID is null

dbfiddle

答案6

得分: 1

以下是翻译好的部分：

尝试以下代码以获得所需的输出。
希望这对您有帮助。

  1. 创建表 t
  2. (
  3. EID varchar(20),
  4. HOLD varchar(20),
  5. [Source] varchar(40)
  6. )
  8. 向表 t 中插入数据
  9. Select '3232','KLME','jame.k@gmail.com'
  10. union all select '    ','KLME','https://google.com'
  11. union all select '    ','EEME','david.e@gmail.com'
  12. union all select '    ','JJIN','Test@gmail.com'
  13. union all select '7232','JJIN','https://google.com'
  15. 更新表 t
  16. 将 t.EID 设置为 t1.EID
  17. 从 t,
  18. (从 t 中选择 EID,HOLD，其中 t.EID 不等于 '') t1
  19. 其中 t1.HOLD = t.hold
  20. 且 t.EID = ''
  22. 从表 t 中选择所有数据
  24. 输出：
  26. EID	    HOLD	Source
  27. ---------------------------
  28. 3232	KLME	jame.k@gmail.com
  29. 3232	KLME	https://google.com
  30.     	EEME	david.e@gmail.com
  31. 7232	JJIN	Test@gmail.com
  32. 7232	JJIN	https://google.com
英文:

Try the below code to get the desired outputs.
Hope this helps.

  1. Create table t
  2. (
  3. EID varchar(20),
  4. HOLD varchar(20),
  5. [Source] varchar(40)
  6. )
  8. insert into t
  9. Select &#39;3232&#39;,&#39;KLME&#39;,&#39;jame.k@gmail.com&#39;
  10. union all select &#39;    &#39;,&#39;KLME&#39;,&#39;https://google.com&#39;
  11. union all select &#39;    &#39;,&#39;EEME&#39;,&#39;david.e@gmail.com&#39;
  12. union all select &#39;    &#39;,&#39;JJIN&#39;,&#39;Test@gmail.com&#39;
  13. union all select &#39;7232&#39;,&#39;JJIN&#39;,&#39;https://google.com&#39;
  15. update t
  16. set t.EID = t1.EID
  17. FROM t,
  18. (Select EID,HOLD from t where t.EID &lt;&gt; &#39;&#39;) t1
  19. where t1.HOLD = t.hold
  20. and t.EID = &#39;&#39;
  22. Select * from t
  24. OUTPUT:
  26. EID	    HOLD	Source
  27. ---------------------------
  28. 3232	KLME	jame.k@gmail.com
  29. 3232	KLME	https://google.com
  30.     	EEME	david.e@gmail.com
  31. 7232	JJIN	Test@gmail.com
  32. 7232	JJIN	https://google.com
  35. </details>

huangapple
  • 本文由 发表于 2023年6月15日 07:18:50
  • 转载请务必保留本文链接：https://go.coder-hub.com/76478155.html
  • sql
  • sql-server
  • sql-server-2012
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定