Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2013-06-11
5
Medium Priority
?
331 Views
Last Modified: 2013-06-15
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
0
Comment
Question by:tommym121
5 Comments
 

Author Comment

by:tommym121
ID: 39239249
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
 
LVL 23

Assisted Solution

by:nemws1
nemws1 earned 664 total points
ID: 39239313
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
 
LVL 5

Assisted Solution

by:DOSLover
DOSLover earned 668 total points
ID: 39239349
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
 
LVL 2

Accepted Solution

by:
vivekkumarSharma earned 668 total points
ID: 39240778
UPDATE a
SET a.Gender = b.Gender
FROM TableX a
 INNER JOIN CustomerTable b
    ON a.CustomerID = b.CustomerID
0
 

Author Closing Comment

by:tommym121
ID: 39250846
Thanks
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

926 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question