How do I copy the column A data to column B?

Posted on 2008-02-01
Medium Priority
Last Modified: 2010-04-21
Board Table
ID      ColumnA                 ColumnB
1        Pending
2         Pending
3         Escalated
4          Pending
5         Completed

Hello. I am using MS-SQL 2005
I need to make few data transfer.
I have a table called Board Table with Column A and B.
Column A data type is nvarchar(20) and Column B is a new column with data type  int.
Also column A has 3 different values that is all mixed in the column(Pending,Escalated,Completed)
I want to make a data copy to ColumnA to ColumnB. If it's Pending, value can 1. If it's Escalated, value can be 2, Completed value can be 3.
How do I make those kind of query.
Thank you
Question by:erin027
  • 3
  • 2
  • 2

Accepted Solution

Yiogi earned 1600 total points
ID: 20801797
UPDATE [Board Table]
              SET ColumnB = CASE WHEN Ltrim(Rtrim(A)) = 'Pending' THEN 1
                                                  WHEN Ltrim(Rtrim(A)) = 'Escalated' THEN 3

Use additional WHEN statements for the remaining of your options.
LVL 15

Assisted Solution

by:Faiga Diegel
Faiga Diegel earned 400 total points
ID: 20801825
UPDATE BoardTable
SET ColumnB = (CASE WHEN ColumnA = 'Pending' THEN 1
                          CASE WHEN ColumnA= 'Escalated' THEN 2
                          CASE WHEN ColumnA = 'Completed' THEN 3 END)

Author Comment

ID: 20801869
Thank you guys.
It worked. One more question
That if I just wanted to copy exactly same data to ColumnB if both column has same datatype?
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.


Expert Comment

ID: 20801875
UPDATE BoardTable SET ColumnB = ColumnA

Author Closing Comment

ID: 31427401
Thank you
LVL 15

Expert Comment

by:Faiga Diegel
ID: 20801948
yes, It should have at least the same data type. Or you would need to convert.


Author Comment

ID: 20802013
When I copy columnA to columnB with your query:
UPDATE BoardTable SET ColumnB = ColumnA

both column's data got NULL.

Why is that?

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Question has a verified solution.

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

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
In this article, we will show how to detach and attach a database and then show how to repair a corrupt database and attach it, If it has some errors. We will show how to detach and attach using SSMS or using T-SQL sentences.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

588 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