Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2013-06-11
5
Medium Priority
?
320 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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.

721 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