..copy data from column A in Table A to column B in Table B?

Using the SQL UPDATE statement how can I copy a columns data say from column A in Table A to column B in Table B?
Note that I am using Microsoft SQL Server 2000


I try doing the following:

UPDATE B SET B.column_B = A.column_A
FROM Table_A A, Table_B B
WHERE A.numID = B.numID AND A.Line = B.Line

but it did not work so I try this

UPDATE Table_B
SET column_B = (SELECT column_A FROM Table_A WHERE Table_A.numID = 233)
WHERE Table_B.numID = 233

When I do this one I get the following error msg:
Server: Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. The statement has been terminated.


I am trying to copy all the values of column A in Table A to column B in Table B.


Any suggestions or hints are welcome

Are you inserting new records into table B based on the values of table A?

I'm fairly sure SELECT INTO is the key to your problem, but I haven't got the code in front of me to provide an answer of any kind.

this might help you out:

http://www.sql-tutorial.net/SQL-SELECT-INTO.asp

Why don’t you use a procedure instead, Try using loops in procedures.

HostBreak,

Currently I am only trying to do this using SQL statements with the Microsoft SQL Server [SQL Query Analyzer]. Perhaps, in the future but not a the moment. Thanks anyways I'll keep that in mind.



D3mon,

Yes I am inserting new records into table B [column B] based on the values of table A [column A].

What do you mean SELECT INTO is the key to my problem?
you mean that I should use it instead of just select... can you elaborate a little bit more on this.

OK, I just look at the link you gave me and I read some the stuff in it.
I think you are right again.

One of the concerns I have is that it looks like to me that the SQL SELECT INTO statement is use for making backup copies of tables. In my scenario table B already exist with columns that have records, except column B which has <NULL> as a record.
The other concern I have is that when I copy the records in table A [column A] into table B [column B] the values of the superkey of both tables have to match.

Anyways, I'll give it a try. Thanks.

D3mon,

The SQL SELECT INTO statement doesn't work if the table already exist.
I try the following sql statement:

SELECT column_A INTO table_B FROM table_A WHERE table_A.numID = 233


and I got the following error from the Microsoft SQL Server 2000:
Server: Msg 2714, Level 16, State 6, Line 1
There is already an object named 'table_B' in the database.


I think my best bet is to try using this SQL statement:
UPDATE table_B
SET column_B = (SELECT column_A FROM table_A WHERE table_A.numID = 233 AND B.numID = 233)
FROM table_B B

But it doesn't work b/c it can only pull one record at a time and it does not like or recognizes the column B.numID
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'numID'.

I thought the same at first, but I have been able to use it to copy items from a basket table into an exisiting order table, so I'll try to get a copy of the query and post it for you.

D3mon,

This SQL UPDATE statement works
UPDATE table_B
SET column_B = (SELECT column_A FROM table_A WHERE table_A.numID = 233 AND B.numID = 233)
FROM table_B B


For some reason the Microsoft SQL Query Analyzer does not like it, so I gave up trying in it and I use a SQL Query standalone application that one of my co-workers created and it work.

It is working... may be the SELECT INTO statement doesn't work in the SQL Query Analyzer too when you have an existing table to where you want to copy the fields, but I am sure it will work on the SQL Query standalone application that one of my co-workers created.


Anyways, Again thank you for all your help.

NOTE: I like your quote "Power is nothing without control", where did you get that?

Sorry, it looks Like it was INSERT INTO instead of SELECT INTO. Here's the query I use, perhaps you can modify it for your own purposes:

Code: Select all

INSERT INTO	tblOrderItem 
	
SELECT		@orderID as 'orderid',
		P.productid,
		multiple as 'quantity',
		P.Price as 'price' 
	
FROM 		tblBasketItems
	
INNER JOIN 	tblProducts P 
	ON 	P.ProductID = tblBasketItems.ProductID
		
WHERE 		tblBasketItems.basketID = @basketID

"Power is nothing without control"

IIRC it was once the advertising slogan for the Pirelli Tire company. Stuck in my mind ever since I first saw it.

But I believe that will Insert new records instead of updating the older ones

Hence my original question:

Are you inserting new records into table B based on the values of table A?