Update columns in a row based on the records from the other table

Question

Hello,

Please have a look at the below image before reading my question.

If you are not able to view the above picture, please click this link to view the image of the database design and the table data.

I want to update the columns like "Parking,AC,TV,locallcalls" in the table "Hotels" with the values from the column "Status" in the table "amenity" by matching the column names(Hotel) with AmenityDesc(in amenity). Hid is the key constraint to relate the tables. Its like a Transpose. Row values in the one table should update the coulmns in other table beased on some condition.

Please help.

My table is having around 10,000 records. if i am using the dynamic query,cursor or loop. the execution is very slow and performance is very bad.

Pls help.

Thanks

I have used subqueries/dynamic queries.Since the number of records are 10000 its very slow.

Is there any other way of handling multiple update queries faster? i am using sqlserver 2000. Pls help

Answer 1

You cound use inner select statements to find the correct values... So for the Parking field it could look like...

UPDATE Hotel 
SET 
 Parking = (SELECT Status FROM Amenity WHERE Amenity.hid = Hotel.hid AND Amenity.AmenityDesc = 'Parking')

A few words to the data model...

I would rename AmenityDesc into Description (that makes Amenity.Description...)

Entity name convention: hotel and Amenity -> Hotel and Amenity

Answer 2

I do not believe you can do this with a single query without resorting to dynamic SQL as the column names are variable and you can not use variables to represent column names in T-Sql

You can do it with a set of individual queries with subqueries to retrieve the data as mentioned by Yves M or with a JOIN as follows

UPDATE hotel
SET    Parking = Amenity.Status
FROM   hotel
JOIN   Amenity ON Amenity.hid = hotel.hid
WHERE  Amenity.AmenityDesc = 'Parking'

and repeat for each column in hotel that you wish to update.

Answer 3

You can use dynamic sql to generate the update statements for you, and either run them manually or uncomment the exec line and have them run immediately

create table #amenity (amenitydesc nvarchar(50))
insert into #amenity(amenitydesc) select distinct amenitydesc from Amenity
declare @amenity nvarchar(50)
declare @sql nvarchar(max)
select @amenity = min(amenitydesc) from #amenity
while @amenity is not null
begin
    select @sql = 'update hotel set ' + @amenity + ' = amenity.status from amenity join hotel on amenity.hid = hotel.hid where amenity.amenityDesc =  ''' + @amenity + '''' 

    --exec(@sql)
    select @sql

    select @amenity = min(amenitydesc) from #amenity where amenitydesc > @amenity
end