Wednesday, January 5, 2011

Another T-SQL Update query with Inner Join

This is a T-SQL Update statement with Inner Join statement. That's really useful.
Datetime is comparison a bit tricky.

DECLARE @p_date DATETIME
DECLARE @L_date DATETIME
SET @p_date = CONVERT( DATETIME, '21 Dec 2010', 106)
SET @L_date = CONVERT( DATETIME, '5 Jan 2011', 106)

UPDATE [PrideAngel].[dbo].[tProfile]
SET [MessageCredits] = (tp.[MessageCredits] + '5' )
FROM [PrideAngel].[dbo].[tProfile] tp Inner join
[PrideAngel].[dbo].[aspnet_Membership] tm
ON tm.[UserId] = tp.UserId
Where tm.[LastLoginDate] >= @p_date and tm.[LastLoginDate] <= @L_date

No comments: