SQL - how to add data to a new column of a table

I have created a table with data. Now I realize I need to add one more column.

I know I can use Alter to add a new column.

Now how am I add this data to the table?

I am using SQL Server 2008
tommym121Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
vivekkumarSharmaConnect With a Mentor Commented:
UPDATE a
SET a.Gender = b.Gender
FROM TableX a
 INNER JOIN CustomerTable b
    ON a.CustomerID = b.CustomerID
0
 
tommym121Author Commented:
My table is coming from this CustomerTable

CustomerID, FirstName, LastName, Age, Gender

I have then create a table called TableX where I put only
Columns

CustomerID, FirstName, LastName

and inserted data to TableX

Now I realize I miss the Gender in TableX

I use Alter to add column Gender into TableX


How can I add gender data to each records

say I have this set of data in CustomerTable

123, Mary, Jane, 23, F
124, Jack, Wood, 34, M
456,Matt, Tripper,67, M
345, Jane, Ford, 45, F

Now in my TableX, I have

123, Mary, Jane
124, Jack, Wood
456,Matt, Tripper
345, Jane, Ford

What SQL I need to write to update my TableX like below (after I add the column 'Gender')

123, Mary, Jane, F
124, Jack, Wood, M
456,Matt, Tripper, M
345, Jane, Ford,  F
0
 
nemws1Connect With a Mentor Database AdministratorCommented:
Do an UPDATE and JOIN to your original table.

UPDATE TableX
SET TableX.Gender = CustomerTable.Gender
FROM TableX
  JOIN CustomerTable
    ON TableX.CustomerID = CustomerTable.CustomerID

Open in new window

0
 
DOSLoverConnect With a Mentor Commented:
Assuming CustomerId is unique, please try this:

update  TableX a 
   set gender = (Select gender from CustomerTable where CustomerID=a.CustomerID)
 where gender is NULL 

Open in new window

0
 
tommym121Author Commented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.