比较表格中的两个值，然后根据条件更新其中一个。

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

Comparing two values of the tables and then updating one of them according to the condition

问题

需要比较两个不同表格中一个名为"Marks"的列的值。

表格 A：-

  1. 姓名   分数
  2. Amit   23
  3. Rahul  12
  4. Gaur   10

表格 B：-

  1. 姓名   分数
  2. Amit   34
  3. Rahul  10
  4. Gaur   10

条件：-
A 的分数不等于 B 的分数
然后在表格 A 中更新
分数 = A - B

最终结果将是：-

  1. 姓名   分数
  2. Amit   -11
  3. Rahul   2
  4. Gaur    0
英文:

need to Compare the values of one named columns of two different tables.

Table A:-

  1. Name Marks
  2. Amit   23
  3. Rahul  12
  4. Gaur   10

Table B:-

  1. Name Marks
  2. Amit   34
  3. Rahul  10
  4. Gaur   10
  6. Condition:-
  7. marks of A != marks of B
  8. then update in table A 
  9. Marks= A-B

Final result will be :-

  1. Name Marks
  2. Amit   -11
  3. Rahul   2
  4. Gaur    10

答案1

得分: 0

只需在名称上联接表格，然后减去值 -

  1. SELECT A.name, CASE WHEN A.Marks <> B.Marks THEN A.Marks - B.Marks ELSE A.Marks END Final_marks
  2.   FROM A
  3.   JOIN B ON A.name = B.name;
英文:

Just join the tables on name and then subtracts the values -

  1. SELECT A.name, CASE WHEN A.Marks &lt;&gt; B.Marks THEN A.Marks - B.Marks ELSE A.Marks END Final_marks
  2.   FROM A
  3.   JOIN B ON A.name = B.name;

答案2

得分: 0

UPDATE TableA
SET Marks = A.Marks - B.Marks
FROM TableA A
JOIN TableB B ON A.Name = B.Name
WHERE A.Marks <> B.Marks;

英文: 
  1. UPDATE TableA
  2. SET Marks = A.Marks - B.Marks
  3. FROM TableA A
  4. JOIN TableB B ON A.Name = B.Name
  5. WHERE A.Marks &lt;&gt; B.Marks;

答案3

得分: 0

你可以使用MERGE语句：

  1. MERGE INTO a
  2. USING b
  3. ON (a.name = b.name)
  4. WHEN MATCHED THEN
  5.   UPDATE
  6.   SET marks = a.marks - b.marks
  7.   WHERE a.marks <> b.marks

对于示例数据：

  1. CREATE TABLE A (Name, Marks) AS
  2. SELECT 'Amit',  23 FROM DUAL UNION ALL
  3. SELECT 'Rahul', 12 FROM DUAL UNION ALL
  4. SELECT 'Gaur',  10 FROM DUAL;
  6. CREATE TABLE b (Name, Marks) AS
  7. SELECT 'Amit',  34 FROM DUAL UNION ALL
  8. SELECT 'Rahul', 10 FROM DUAL UNION ALL
  9. SELECT 'Gaur',  10 FROM DUAL;

然后在MERGE之后，表A包含：

名称 分数
Amit -11
Rahul 2
Gaur 10

fiddle

英文:

You can use a MERGE statement:

  1. MERGE INTO a
  2. USING b
  3. ON (a.name = b.name)
  4. WHEN MATCHED THEN
  5.   UPDATE
  6.   SET marks = a.marks - b.marks
  7.   WHERE a.marks &lt;&gt; b.marks

Which, for the sample data:

  1. CREATE TABLE A (Name, Marks) AS
  2. SELECT &#39;Amit&#39;,  23 FROM DUAL UNION ALL
  3. SELECT &#39;Rahul&#39;, 12 FROM DUAL UNION ALL
  4. SELECT &#39;Gaur&#39;,  10 FROM DUAL;
  6. CREATE TABLE b (Name, Marks) AS
  7. SELECT &#39;Amit&#39;,  34 FROM DUAL UNION ALL
  8. SELECT &#39;Rahul&#39;, 10 FROM DUAL UNION ALL
  9. SELECT &#39;Gaur&#39;,  10 FROM DUAL;

Then after the MERGE, table A contains:

NAME MARKS
Amit -11
Rahul 2
Gaur 10

fiddle

huangapple
  • 本文由 发表于 2023年6月22日 12:34:40
  • 转载请务必保留本文链接：https://go.coder-hub.com/76528628.html
  • oracle
  • sql
匿名

发表评论

匿名网友

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

确定