[RESOLVED] Help on Update table

[GaryMazzone]

Using SQL Server 2005

I have a select statement that finds the data I need

Select * from histmasteruser HMU Inner Join meetingbroker.dbo.masteruser MU On Mu.MasterUserID = HMU.MasterUserId and MU.LastModifiedDate < HMU.LastModifiedDate]

I There a way I can use that in an Update Statement to Update the table Marked MU with the data in specified fields from the table marked HMU?

UPDATE TABLE statement?

[si_the_geek]

If memory serves it is like this:

UPDATE   meetingbroker.dbo.masteruser MU
SET fieldname = HMU.fieldname
...

FROM     histmasteruser HMU 
WHERE    Mu.MasterUserID = HMU.MasterUserId AND MU.LastModifiedDate < HMU.LastModifiedDate

..but I have a sneaking suspicion that you can't use an alias for the MU table, so need to spell it out each time.

[riteshjain1982]

you cant use Alias and i doubt even if you can use meetingbroker.dbo. in Update query.

[GaryMazzone]

Here is what I used (it does work)

PRINT 'Tring to Update records'
            UPDATE MeetingBroker.dbo.MasterUser
                SET MasterUserGuid = HMU.MasterUserGuid,
                    UserName = HMU.UserName,
                    Password = HMU.Password,
                    Email = HMU.Email,
                    IsTemporaryPasswordSet = HMU.IsTemporaryPasswordSet,
                    DatabaseId = HMU.DatabaseId ,
                    CreateDate = HMU.CreateDate,
                    LastModifiedDate = HMU.LastModifiedDate,
                    LastModifiedByUserId = HMU.LastModifiedByUserId
            FROM MeetingBroker.dbo.MasterUser MU
            INNER JOIN histmasteruser HMU ON Mu.MasterUserID = HMU.MasterUserId and MU.LastModifiedDate < HMU.LastModifiedDate

SI I got it just after I posed.