Solved

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

Posted on 2013-06-11
5
309 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 166 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 167 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 167 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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

820 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