在另一列中插入添加的列值 [英] inserting added column values in another column

问题描述

我有一张名为 student 的表，其中包含以下列名称和数据

<前>NAME MARK2 MARK3 mark1 共苏里亚 85 95 123普里亚 75 95 25毗湿奴 88 75 27

有什么方法可以添加列mark1和mark2和mark3并插入这些添加的值，你们可以向我建议使用sqlplus数据库的this.iam

解决方案

试试这个方法

更新学生SET 总计 = 标记 1 + 标记 2 + 标记 3；

最好还是创建一个视图

创建视图 vw_student ASSELECT name, mark3, mark2, mark1, mark3 + mark2 + mark1 总共来自学生；

改用它

SELECT * FROM vw_student;

这是两种方法的SQLFiddle演示><小时>

UPDATE:根据您的评论，您可以创建一个触发器(实际上两个用于 INSERT，第二个用于 UPDATE)来填充 total 自动列

创建触发器 tg_bi_student在插入学生之前每行SET NEW.total = NEW.mark1 + NEW.mark2 + NEW.mark3;创建触发器 tg_bu_student在学生更新之前每行SET NEW.total = NEW.mark1 + NEW.mark2 + NEW.mark3;

这里是 SQLFiddle 的触发器方法><小时>

第二次更新 由于您实际使用 Oracle，您可以将 total 定义为计算列

CREATE TABLE 学生(名称 VARCHAR2(32),MARK2 号，MARK3 号，标记 1 号，生成的总数始终为(标记 1 + 标记 2 + 标记 3));

您可以通过这种方式更改表的架构

ALTER TABLE student DROP COLUMN total;ALTER TABLE student ADD total NUMBER GENERATED ALWAYS AS (mark1 + mark2 + mark3);

这是SQLFiddle演示

I am having one table named student having the following column names and data's

NAME   MARK2      MARK3      mark1      total  
surya  85         95         123        
priya   75         95         25         
vishnu   88         75        27  

is there any way to add column mark1 and mark2 and mark3 and insert that added values in total can you guys suggest me about this.iam using sqlplus database

解决方案

Try it this way

UPDATE student
   SET total = mark1 + mark2 + mark3;

Better yet just create a view

CREATE VIEW vw_student AS
SELECT name, mark3, mark2, mark1, mark3 + mark2 + mark1 total
  FROM student;

and use it instead

SELECT * FROM vw_student;

Here is SQLFiddle demo for both approaches

---

UPDATE: Based on your comments you can create a trigger (actually two one for INSERT and the second for UPDATE) to populate total column automatically

CREATE TRIGGER tg_bi_student
BEFORE INSERT ON student
FOR EACH ROW
  SET NEW.total = NEW.mark1 + NEW.mark2 + NEW.mark3;

CREATE TRIGGER tg_bu_student
BEFORE UPDATE ON student
FOR EACH ROW
  SET NEW.total = NEW.mark1 + NEW.mark2 + NEW.mark3;

Here is SQLFiddle for a trigger approach

---

2ND UPDATE Since you actually using Oracle you can just define total as a computed column

CREATE TABLE student
(
  NAME  VARCHAR2(32), 
  MARK2 NUMBER, 
  MARK3 NUMBER, 
  mark1 NUMBER, 
  total NUMBER GENERATED ALWAYS AS (mark1 + mark2 + mark3)
);

You can change your table's schema this way

ALTER TABLE student DROP COLUMN total;
ALTER TABLE student ADD total NUMBER GENERATED ALWAYS AS (mark1 + mark2 + mark3);

Here is SQLFiddle demo

这篇关于在另一列中插入添加的列值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！