Solved

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

Posted on 2013-06-11
5
306 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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
Viewers will learn how the fundamental information of how to create a table.

867 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now