How to Alter Multiple Columns at Once in SQL Server?
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 FIRM inside the database w3wiki. This table has 4 columns namely FIRST_NAME, LAST_NAME, SALARY, and BONUS containing the first names, last names, salaries, and bonuses of the members in a firm.
Query:
CREATE TABLE FIRM( FIRST_NAME VARCHAR(20), LAST_NAME VARCHAR(20), SALARY INT, BONUS INT );
Output:
Step 4: Describe the structure of the table FIRM.
Query:
EXEC SP_COLUMNS FIRM;
Output:
Step 5: Insert 5 rows into the FIRM table.
Query:
INSERT INTO FIRM VALUES('ALEX','STONE',10000,1000); INSERT INTO FIRM VALUES('MATT','JONES',20000,2000); INSERT INTO FIRM VALUES('JOHN','STARK',30000,3000); INSERT INTO FIRM VALUES('GARY','SCOTT',40000,4000); INSERT INTO FIRM VALUES('RICHARD','WALT',50000,5000);
Output:
Step 6: Display all the rows of the FIRM table.
Query:
SELECT * FROM FIRM;
Output:
Step 7: Alter multiple(2) columns of the table FIRM by adding 2 columns to the table simultaneously. The 2 columns are JOINING_DATE and LEAVING_DATE containing the date of joining of the member and the date of leaving of the member. Use the keyword ALTER and ADD to achieve this.
Syntax:
ALTER TABLE TABLE_NAME ADD COLUMN1 DATA_TYPE, COLUMN2 DATA_TYPE........;
Query:
ALTER TABLE FIRM ADD JOINING_DATE DATE, LEAVING_DATE DATE;
Output:
Step 8: Describe the structure of the altered table FIRM.
Query:
EXEC SP_COLUMNS FIRM;
Note: The table description now has 2 extra columns.
Output:
Step 9: Update the table by inserting data into the 2 newly added columns of the FIRM table. Use keyword UPDATE.
Syntax:
UPDATE TABLE_NAME SET COLUMN1=VALUE, COLUMN2=VALUE WHERE CONDITION;
Query:
UPDATE FIRM SET JOINING_DATE='01-JAN-2001', LEAVING_DATE='01-JAN-2002' WHERE FIRST_NAME='ALEX'; UPDATE FIRM SET JOINING_DATE='02-FEB-2001', LEAVING_DATE='02-FEB-2002' WHERE FIRST_NAME='MATT'; UPDATE FIRM SET JOINING_DATE='03-MAR-2001', LEAVING_DATE='03-MAR-2002' WHERE FIRST_NAME='JOHN'; UPDATE FIRM SET JOINING_DATE='04-APR-2001', LEAVING_DATE='04-APR-2002' WHERE FIRST_NAME='GARY'; UPDATE FIRM SET JOINING_DATE='05-MAY-2001', LEAVING_DATE='05-MAY-2002' WHERE FIRST_NAME='RICHARD';
Output:
Step 10: Display all the rows of the altered FIRM table.
Query:
SELECT * FROM FIRM;
Note: The displayed table now has 2 extra columns.
Output:
Step 11: Alter multiple(2) columns of the table FIRM by dropping 2 columns from the table simultaneously. The 2 columns are JOINING_DATE and LEAVING_DATE containing the date of joining of the member and the date of leaving of the member. Use the keyword ALTER and DROP to achieve this.
Syntax:
ALTER TABLE TABLE_NAME DROP COLUMN COLUMN1, COLUMN2........;
Query:
ALTER TABLE FIRM DROP COLUMN JOINING_DATE,LEAVING_DATE;
Output:
Step 12: Describe the structure of the altered table FIRM.
Query:
EXEC SP_COLUMNS FIRM;
Note: The table description now has 2 fewer columns.
Output:
Step 13: Display all the rows of the altered FIRM table.
Query:
SELECT * FROM FIRM;
Note: The displayed table now has 2 fewer columns.
Output: