英文:
Comparing two values of the tables and then updating one of them according to the condition
问题
需要比较两个不同表格中一个名为"Marks"的列的值。
表格 A：-
姓名 分数
Amit 23
Rahul 12
Gaur 10
表格 B：-
姓名 分数
Amit 34
Rahul 10
Gaur 10
条件：-
A 的分数不等于 B 的分数
然后在表格 A 中更新
分数 = A - B
最终结果将是：-
姓名 分数
Amit -11
Rahul 2
Gaur 0
英文:
need to Compare the values of one named columns of two different tables.
Table A:-
Name Marks
Amit 23
Rahul 12
Gaur 10
Table B:-
Name Marks
Amit 34
Rahul 10
Gaur 10
Condition:-
marks of A != marks of B
then update in table A
Marks= A-B
Final result will be :-
Name Marks
Amit -11
Rahul 2
Gaur 10
答案1
得分: 0
只需在名称上联接表格，然后减去值 -
SELECT A.name, CASE WHEN A.Marks <> B.Marks THEN A.Marks - B.Marks ELSE A.Marks END Final_marks
FROM A
JOIN B ON A.name = B.name;
英文:
Just join the tables on name and then subtracts the values -
SELECT A.name, CASE WHEN A.Marks <> B.Marks THEN A.Marks - B.Marks ELSE A.Marks END Final_marks
FROM A
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;
英文:
UPDATE TableA
SET Marks = A.Marks - B.Marks
FROM TableA A
JOIN TableB B ON A.Name = B.Name
WHERE A.Marks <> B.Marks;
答案3
得分: 0
你可以使用MERGE语句：
MERGE INTO a
USING b
ON (a.name = b.name)
WHEN MATCHED THEN
UPDATE
SET marks = a.marks - b.marks
WHERE a.marks <> b.marks
对于示例数据：
CREATE TABLE A (Name, Marks) AS
SELECT 'Amit', 23 FROM DUAL UNION ALL
SELECT 'Rahul', 12 FROM DUAL UNION ALL
SELECT 'Gaur', 10 FROM DUAL;
CREATE TABLE b (Name, Marks) AS
SELECT 'Amit', 34 FROM DUAL UNION ALL
SELECT 'Rahul', 10 FROM DUAL UNION ALL
SELECT 'Gaur', 10 FROM DUAL;
然后在MERGE之后，表A包含：
| 名称 | 分数 |
|---|---|
| Amit | -11 |
| Rahul | 2 |
| Gaur | 10 |
英文:
You can use a MERGE statement:
MERGE INTO a
USING b
ON (a.name = b.name)
WHEN MATCHED THEN
UPDATE
SET marks = a.marks - b.marks
WHERE a.marks <> b.marks
Which, for the sample data:
CREATE TABLE A (Name, Marks) AS
SELECT 'Amit', 23 FROM DUAL UNION ALL
SELECT 'Rahul', 12 FROM DUAL UNION ALL
SELECT 'Gaur', 10 FROM DUAL;
CREATE TABLE b (Name, Marks) AS
SELECT 'Amit', 34 FROM DUAL UNION ALL
SELECT 'Rahul', 10 FROM DUAL UNION ALL
SELECT 'Gaur', 10 FROM DUAL;
Then after the MERGE, table A contains:
| NAME | MARKS |
|---|---|
| Amit | -11 |
| Rahul | 2 |
| Gaur | 10 |
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论