带有连接和动态列名的update语句 [英] update statement with join and dynamic column name

查看:72
本文介绍了带有连接和动态列名的update语句的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

大家好,



我在使用动态列名更新表时遇到问题,而使用update语句更新内部联接。请查看我的代码。



hi all,

i m having a problem with updating a table with dynamic column name and inner join with update statement. please view my code.

ALTER procEDURE [dbo].[UtilitySaveHomepageHotels](
	@HomePageHotel		XML = NULL,
	@ColumnName			varchar(100)
	)
	AS
	BEGIN
		SET NOCOUNT ON;	
		BEGIN TRY
			BEGIN TRAN
			
			DECLARE @tblHomePageHotel TABLE(AutoID INT IDENTITY,
											OriginalHotelID int, 
											HotelName varchar(500), 
											HotelAddress varchar(500),
											TripAdvisorRatingURL varchar(500),
											TripAdvisorReviewCount varchar(50),
											CreatedBy int,
											StarRating varchar(50),
											Cur varchar(100),
											SiteID	bigint
											)
			
				IF @HomePageHotel IS NOT NULL
					BEGIN
						INSERT INTO @tblHomePageHotel(OriginalHotelID, 
														HotelName , 
														HotelAddress ,
														TripAdvisorRatingURL ,
														TripAdvisorReviewCount,
														CreatedBy,
														StarRating,
														Cur ,
														SiteID
														)
						SELECT 	d.v.value('(OriginalHotelID/text())[1]','INT'),
								d.v.value('(HotelName/text())[1]','VARCHAR(500)'),
								d.v.value('(HotelAddress/text())[1]','VARCHAR(500)'),
								d.v.value('(TripAdvisorRatingURL/text())[1]','VARCHAR(500)'),
								d.v.value('(TripAdvisorReviewCount/text())[1]','VARCHAR(50)'),
								d.v.value('(CreatedBy/text())[1]','INT'),
								d.v.value('(StarRating/text())[1]','VARCHAR(50)'),
								d.v.value('(Cur/text())[1]','NVARCHAR(100)'),
								d.v.value('(SiteID/text())[1]','BIGINT')
								FROM @HomePageHotel.nodes('/HotelList/Hotel') d(v)
								
						SET @ColumnName = 'hh.' + @ColumnName
						print @ColumnName
								
						UPDATE  hh
						SET		hh.HotelName = thh.HotelName,
								hh.HotelAddress = thh.HotelAddress,
								hh.TripAdvisorRatingURL = thh.TripAdvisorRatingURL,
								hh.TripAdvisorReviewCount = thh.TripAdvisorReviewCount,
								hh.CreatedBy = thh.CreatedBy,
								hh.StarRating = thh.StarRating,
								@ColumnName = thh.Cur,
								hh.SiteID = thh.SiteID,
								hh.CreatedDate = GETDATE()														
						FROM HomepageHotels	hh
						INNER JOIN @tblHomePageHotel thh ON thh.OriginalHotelID = hh.OriginalHotelID
						WHERE hh.CreatedBy = thh.CreatedBy
						
						select thh.Cur from @tblHomePageHotel thh
					END
				
			COMMIT TRAN
		END TRY								
		BEGIN CATCH							
			IF @@TRANCOUNT>0				
				ROLLBACK TRAN						
			PRINT Error_Message()			
			DECLARE @msg VARCHAR(MAX)
			SELECT @msg = ERROR_MESSAGE()
			
			RAISERROR('Error in %s: %s', 16, 1, 'UtilitySaveHomepageHotels', @msg)		
		END CATCH				
	END







我的查询工作正常但是我在带有参数的update语句中传递的字段是没有更新。请帮帮我。




my query is working properly but the field i m passing in update statement with parameter is not updated. please help me.

推荐答案

它不会得到更新。您必须使用动态列编写动态sql以进行更新。例如:



It will not get updated. You will have to write dynamic sql for update using dynamic columns. Eg:

declare @sql varchar(100);

set @sql='select * from test'
exec(@sql)





希望它有所帮助!!!



Hope it helps!!!


UPDATE ......

SET hh。 HotelName = thh.HotelName,

hh.HotelAddress = thh.HotelAddress,

hh.TripAdvisorRatingURL = thh.TripAdvisorRatingURL,

hh.TripAdvisorReviewCount = thh.TripAdvisorReviewCount,

hh.CreatedBy = thh.CreatedBy,

hh.StarRating = thh.StarRating,

AUD = CASE WHE @ColumnName ='AUD'那么thh.Cur ELSE AUD END,

CAD = CASE当@ColumnName ='CAD'那么thh.Cur ELSE CAD END,













。 hh.SiteID = thh.SiteID,

hh.CreatedDate = GETDATE()

FROM HomepageHotels hh

INNER JOIN @tblHomePageHotel thh ON thh。 OriginalHotelID = hh.OriginalHotelID

WHERE hh.CreatedBy = thh.CreatedBy





只是用过的时候和解决了这个问题。
UPDATE hh
SET hh.HotelName = thh.HotelName,
hh.HotelAddress = thh.HotelAddress,
hh.TripAdvisorRatingURL = thh.TripAdvisorRatingURL,
hh.TripAdvisorReviewCount = thh.TripAdvisorReviewCount,
hh.CreatedBy = thh.CreatedBy,
hh.StarRating = thh.StarRating,
AUD = CASE WHEN @ColumnName = 'AUD' THEN thh.Cur ELSE AUD END,
CAD = CASE WHEN @ColumnName = 'CAD' THEN thh.Cur ELSE CAD END,

.
.
.
.
.
. hh.SiteID = thh.SiteID,
hh.CreatedDate = GETDATE()
FROM HomepageHotels hh
INNER JOIN @tblHomePageHotel thh ON thh.OriginalHotelID = hh.OriginalHotelID
WHERE hh.CreatedBy = thh.CreatedBy


just used case when and solved the problem.


这篇关于带有连接和动态列名的update语句的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆