Transfer data from one table to another in sql server

Question

I have two tables, one of which I don't need anymore. I want to transfer the piece of data i need from the obsolete table, into the table I'm going to keep. There are bookingid columns in both tables, which I can use to match the rows up. Its a 1 to 0 or 1 relationship. I've looked around and built up this query to accomplish the transfer, but I'm getting a could not be bound error on bookingtoupdate.bookingid

WITH bookingtoupdate (bookingid) AS
(
    SELECT bookingid 
    FROM bookings
)
UPDATE bookings 
SET meetinglocation = (SELECT business.name
                       FROM abk_Locations 
                       INNER JOIN business ON dbo.abk_Locations.IP_Number = business.businessid
                       WHERE        
                           (dbo.abk_Locations.Booking_Number = bookingtoupdate.bookingid)
                      ) 
WHERE 
    bookingid = bookingtoupdate.bookingid

Are there any obvious issues with my code?

I referred the following pages...

http://msdn.microsoft.com/en-us/library/ms175972.aspx SQL Server FOR EACH Loop

score 2 · Accepted Answer · answered Nov 04 '13 at 16:32

You declare bookingtoupdate but you don't select anything from it. That's why it can't be bound.

Here is a simplified query to do what you need without CTE

UPDATE bookings 
SET meetinglocation = business.name
FROM bookings
INNER JOIN abk_Locations ON abk_Locations.Booking_Number = bookings.bookingid
INNER JOIN business ON dbo.abk_Locations.IP_Number = business.businessid

Transfer data from one table to another in sql server

1 Answers1