Update columns in a row based on the records from the other table
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 "amen开发者_运维知识库ity" 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
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
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.
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
精彩评论