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

问题描述

大家好，



我在使用动态列名更新表时遇到问题，而使用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屋！