Update query, set field values for recID 1 = to RecID 2

[awaria]

SQL Server 2000
What is the syntax for an update statement where you set the fields values for record 1 equal to the values in record 2, records are in the same table?

Thanks,

awaria

 

[gmmastros]

It can be tricky. Can you show some sample data and expected results?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[awaria]

It's a user security table. I want to be able to set an existing user's security to that of an existing user in the table. This is for SSRS. Columns are username and the dept code for all depts. Values in dept columns are set to either 1 or 0. Script
reads values and allows user to view data for any dept with a value of 1.


Columns:
Username | 100 | 200 | 250 | etc. . .

asmith 1 0 1

User2 set to 1 0 1

awaria

Want new use

 

[gmmastros]

Sounds like you know the user you want to copy from and the user you want to copy to. If that is the case then...

Declare @Users Table(UserName VarChar(20), Col1 Int, Col2 Int, Col3 Int)

Insert Into @Users Values('ASmith', 1, 0, 1)
Insert Into @Users Values('NewUser', NULL, NULL, NULL)

Update	CopyTo
Set     CopyTo.Col1 = CopyFrom.Col1,
        CopyTo.Col2 = CopyFrom.Col2,
        CopyTo.Col3 = CopyFrom.Col3
From    @Users As CopyFrom
        Inner Join @Users As CopyTo
           On  CopyFrom.UserName = 'ASmith'
           And CopyTo.UserName = 'NewUser'
           
Select * From @Users

Note that this code creates a table variable so you can see how this functionality works. If you are satisfied with it, just change the name of the table and columns.

Make sense?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom