CODEWITHSUNDEEP
sqlsql-servert-sqlalter
Tony
Tony

Reputation: 121

Adding a new column with data

Below is my table:

Id  Name    City    Managerid  
------------------------------
1   Shawn   NYC     3
2   Kevin   Mumbai  4
3   Ryan    Mumbai  1
4   Kanye   SF      1

Now I want to add a new column called 'Gender' with values M,M,M,F in 4 rows respectively.

I first added a new column using:

alter table employee add gender varchar(50)

Now I understand I can add data one by one by using:

update employee 
set gender = 'M' 
where id = 1

But how do I add all 4 data at once?

Upvotes: 1

Views: 6685

Answers (2)

Dai
Dai

Reputation: 155015

  1. Create and populate a table-valued variable @genderInfo with the data you want to add to the new column.
  2. Add the column to the destination table, note it has to be NULLable to begin with.
  3. Perform an UPDATE with a JOIN with the original table-valued variable.
  4. Change the newly added column to NOT NULL - assuming all rows now have a value.

Like so:

-- 1:
DECLARE @genderInfo AS TABLE
(
    EmployeeId int PRIMARY KEY,
    Gender varchar(50) NOT NULL
)

INSERT INTO @genderInfo ( EmployeeId, Gender ) VALUES
( 1, 'M' ),
( 2, 'M' ),
( 3, 'M' ),
( 4, 'F' );

-- 2:
ALTER TABLE
    Employees
ADD
    Gender varchar(50) NULL;

-- 3:
UPDATE
    e
SET
    e.Gender = gi.Gender
FROM
    Employees AS E
    INNER JOIN @genderInfo AS gi ON e.EmployeeId = gi.EmployeeId

-- 4:
ALTER TABLE
    Employees
ALTER COLUMN
    Gender varchar(50) NOT NULL;

Upvotes: 2

user7396598
user7396598

Reputation: 1289

You don't, unless there is some other piece of relational data that allows the engine to group the rows in someway. For example if all the males were in Mumbai. Alternatively, you can do it in 2 statements.

update employee set gender= 'M' where id IN (1, 2, 3)
update employee set gender= 'F' where id IN (4)

But this is still not useful if you have a large number of males and females that need to be updated after adding the column.

Upvotes: 2

Related Questions