Examples of Insert and Update in SQL

The following code shows some Examples of Insert and Update in SQL.

For the purpose of the insert and update queries, we take following institute database.

At first, we add certain rows in the Student table having the following schema.

Since we don’t need the country column, we can remove it by the following command.

alter table Student drop column country;

After that, we can insert the rows.

insert into Student(enrolment_no, student_name, address, contact_no, gender, emailid, course_id) values(112, 'amit', 'Delhi', '8899776655', 'M', 'a@gmail.com', 20)

insert into Student(enrolment_no, student_name, address, contact_no, gender, emailid, course_id) values(113, 'sumy', 'Delhi', '4444776655', 'F', 'S@gmail.com', 20)

insert into Student(enrolment_no, student_name, address, contact_no, gender, emailid, course_id) values(114, 'JAI', 'Agra', '4444776655', 'M', 'j@gmail.com', 17)

insert into Student(enrolment_no, student_name, address, contact_no, gender, emailid, course_id) values(115, 'Kirvi', 'Panipat', '1122776655', 'F', 'k@gmail.com', 17)

insert into Student(enrolment_no, student_name, address, contact_no, gender, emailid, course_id) values(116, 'Tina', 'Udupi', '8888776655', 'F', 't@gmail.com', 17)

Similarly, we can add rows in other tables too. So, first let us insert some rows in the Subject table which has following schema.

However, before inserting values, we need to create the composite primary key of the table as (course_id, subject_id) as follows.

alter table Subject drop primary key, add primary key (course_id, subject_id)

insert into Subject(course_id, subject_id, subject_name, maximum_marks) values(20, '201', 'C#', 100)
insert into Subject(course_id, subject_id, subject_name, maximum_marks) values(20, '202', 'BlockChain', 100)
insert into Subject(course_id, subject_id, subject_name, maximum_marks) values(20, '203', 'Python', 100)
insert into Subject(course_id, subject_id, subject_name, maximum_marks) values(20, '204', 'Metaverse', 100)
insert into Subject(course_id, subject_id, subject_name, maximum_marks) values(17, '201', 'Fintech', 100)
insert into Subject(course_id, subject_id, subject_name, maximum_marks) values(17, '202', 'ITM', 100)
insert into Subject(course_id, subject_id, subject_name, maximum_marks) values(17, '203', 'ED', 100)

After executing above queries, we get following tables.

Updating Table Data

Once we have data in our tables, we can modify it using the Update SQL command. For instance, suppose we need to change the maximum marks in all subjects to 200 where course_id is 17. So, we can run the following command.

update Subject set maximum_marks=200 where course_id=17

As a result of executing the above command, three rows are update. So we get the following table.

Updating Table Data

Further Reading