Adding a new column with data

Question

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?

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. — user7396598
– user7396598, Commented Apr 11, 2018 at 23:33
Your Gender column should be bit (if using a hetereonormative model) or tinyint if you have a domain enum you can use, or at most char(1), but using varchar(50) could lead to meaningless data unless you have rigid check constraints - or you really mean to allow free-text entry. — Dai
– Dai, Commented Apr 11, 2018 at 23:37

user7396598 · Accepted Answer · 2018-04-11 23:34:52Z

2

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.

answered Apr 11, 2018 at 23:34

user7396598

1,2899 silver badges6 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Dai · Accepted Answer · 2018-04-11 23:42:08Z

Create and populate a table-valued variable @genderInfo with the data you want to add to the new column.
Add the column to the destination table, note it has to be NULLable to begin with.
Perform an UPDATE with a JOIN with the original table-valued variable.
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;

I like this work around, but I'd like to point out he is still typing an entry for each row that existed before he made the column addition. LOL, this really is the long way around writing multiple update statements.

Collectives™ on Stack Overflow

Adding a new column with data

2 Answers 2

Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Related