SQL Query to Update All Columns in a Table
In SQL, sometimes we need to write a single query to update the values of all columns in a table. We will use the UPDATE keyword to achieve this. For this, we use a specific kind of query shown in the below demonstration. For this article, we will be using the Microsoft SQL Server as our database and Select keyword.
Step 1: Create a Database. For this use the below command to create a database named w3wiki.
Query:
CREATE DATABASE w3wiki
Output:
Step 2: Use the w3wiki database. For this use the below command.
Query:
USE w3wiki
Output:
Step 3: Create a table of SALARY inside the database w3wiki. This table has 2 columns namely MONTHLY_SALARY and ANNUAL_SALARY containing the monthly and annual salaries of the workers in a factory.
Query:
CREATE TABLE SALARY( MONTHLY_SALARY INT, ANNUAL_SALARY INT);
Output:
Step 4: Describe the structure of the table SALARY.
Query:
EXEC SP_COLUMNS SALARY;
Output:
Step 5: Insert 5 rows into the SALARY table.
Query:
INSERT INTO SALARY VALUES(10000,120000); INSERT INTO SALARY VALUES(20000,240000); INSERT INTO SALARY VALUES(15000,180000); INSERT INTO SALARY VALUES(30000,360000); INSERT INTO SALARY VALUES(25000,300000);
Output:
Step 6: Display all the rows of the SALARY table.
Query:
SELECT * FROM SALARY;
Output:
Step 7: Update all the columns of the table SALARY. Increase the value of all entries of the MONTHLY_SALARY column by 1000, accordingly, increase the value of all entries of the ANNUAL_SALARY column by 12*1000=12000.
Syntax:
UPDATE TABLE_NAME SET COLUMN1=VALUE1, COLUMN2 = VALUE2;
Query:
UPDATE SALARY SET MONTHLY_SALARY = MONTHLY_SALARY+1000,ANNUAL_SALARY= ANNUAL_SALARY+12000;
Output:
Step 8: Display all the rows of the updated SALARY table.
Query:
SELECT * FROM SALARY;
Output: