Open In App

How to Update All Rows in SQL?

Improve
Improve
Like Article
Like
Save
Share
Report

In SQL, sometimes situations arise to update all the rows of the table. We will use the UPDATE command to achieve this in SQL. For this article, we will be using the Microsoft SQL Server as our database.

Syntax:  

Without WITH conditional clause

UPDATE table_name SET column1 = value1, column2 = value2;

With WITH conditional clause

UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;

Step 1: Create a Database. For this use the below command to create a database named GeeksForGeeks.

Query:

CREATE DATABASE GeeksForGeeks

Output:

Step 2: Use the GeeksForGeeks database. For this use the below command.

Query:

USE GeeksForGeeks

Output:

Step 3: Create a table STUDENT_MARKS inside the database GeeksForGeeks. This table has 4 columns namely STUDENT_ID, STUDENT_NAME,  STUDENT_STANDARD, ENGLISH, MATHS, and PHYSICS containing student id, student name, standard, and marks of various subjects.

Query:

CREATE TABLE STUDENT_MARKS(
STUDENT_ID INT,
STUDENT_NAME VARCHAR(20),
STUDENT_STANDARD INT,
ENGLISH INT,
MATHS INT,
PHYSICS INT);

Output:

Step 4: Insert 5 rows into the MARKS table. 

Query:

INSERT INTO STUDENT_MARKS VALUES(121,'ABHIGYAN SHARMA',5,90,85,80);
INSERT INTO STUDENT_MARKS VALUES(133,'RUTURAJ GAIKWAD',5,76,66,91);
INSERT INTO STUDENT_MARKS VALUES(146,'AJAY KUMAR',5,87,79,90);
INSERT INTO STUDENT_MARKS VALUES(151,'RAVI GUPTA',6,91,51,67);
INSERT INTO STUDENT_MARKS VALUES(160,'YUVI CHAHAL',6,79,92,82);

Output:

Step 5: Display all the rows of the MARKS table including the 0(zero) values.

Query:

SELECT * FROM STUDENT_MARKS;

Output:

Step 7: Suppose the new academic year has started. So we need to increase(update) the STUDENT_STANDARD by 1 for all rows. We will not use the WHERE clause here because we have to update all the rows. Then display the table.

Query:

UPDATE STUDENT_MARKS SET STUDENT_STANDARD=STUDENT_STANDARD+1;
SELECT * FROM STUDENT_MARKS;

Output:

Step 8: Some new changes in the school administration state that only the first 4 letters of the student’s name be displayed. So we need to UPDATE the name to its substring comprising of only the first 4 letters. We will not use the WHERE clause here because we have to update all the rows. Then display the table.

Query:

UPDATE STUDENT_MARKS SET STUDENT_NAME=SUBSTRING(STUDENT_NAME,1,4);
SELECT * FROM STUDENT_MARKS;

Output:

Step 9: Suppose the Math teacher wants to increase the marks of all students by 5. So we need to UPDATE the MATHS column, so we increase the value by 5. We will not use the WHERE clause here because we have to update all the rows. Then display the table.

Query:

UPDATE STUDENT_MARKS SET MATHS=MATHS+5;
SELECT * FROM STUDENT_MARKS;

Output:

Hence, in the above-stated ways, we can update all the rows of the table using the UPDATE command. We won’t use the WHERE clause along with it as all rows need to be updated.


Last Updated : 28 Oct, 2021
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads