SQL Query moving data between fields

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SQL Query moving data between fields

Author

Topic

Looper
Yak Posting Veteran

68 Posts

Posted - 2011-07-14 : 10:12:51

I have 3 fields in a table Auth1, Auth2 and Auth3 - I want to write a query that will do the following:

If the @UserId exists in Auth1 and Auth2 and Auth3 are Null then delete the row, however if the @UserId exists in any of the other Auth2 or Auth3 I want to move the values up one or if @UserId exists in Auth1 and there are values in either Auth2 or Auth3 I want to move them all up one overwriting the values.

e.g = @UserId = 1 - Trying to get rid of this value but should only delete if in Auth1 else should move up one.

Auth1 = 1, Auth2 = Null, Auth3 = Null
row should be Deleted

Auth1 = 1, Auth2 = 2, Auth3 = Null
should become Auth1 = 2, Auth2 = Null, Auth3 = Null

Auth1 = 1, Auth2 = 2, Auth3 = 3
should become Auth1 = 2, Auth2 = 3, Auth3 = Null

Auth1 = 4, Auth2 = 1, Auth3 = Null
should become Auth1 = 4, Auth2 = Null, Auth3 = Null

Auth1 = 4, Auth2 = 5, Auth3 = 1
should become Auth1 = 4, Auth2 = 5, Auth3 = Null

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-07-14 : 11:03:33

[code]
update tbl
set auth1 = nullif(auth1, @UserID),
auth2 = nullif(auth2, @UserID),
auth3 = nullif(auth3, @UserID)
where auth1 = @UserID
or auth2 = @UserID
or auth3 = @UserID

update tbl
set auth1 = auth2,
auth2 = NULL
where auth1 is null
and auth2 is not null

update tbl
set auth2 = auth3,
auth3 = NULL
where auth2 is null
and auth3 is not null

delete tbl
where auth1 is null
and auth2 is null
and auth3 is null
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources