带有连接和动态列名的update语句 [英] update statement with join and dynamic column name
本文介绍了带有连接和动态列名的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屋!
查看全文