Sql Update with some conditioning.

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 2008 Forums

Transact-SQL (2008)

Sql Update with some conditioning.

Author

Topic

Shadowrack
Starting Member

2 Posts

Posted - 2011-01-31 : 13:10:19

Hello Dear Forum,

I have a newbie question about updating a row.
I'm developing an ASP.NET website, Using formview in edit mode only inside a page.
What I'm trying to achieve:
When a user enter all details and press the update, he goes to the update method which will check if the "User_Name" does not present in any other rows on the table to keep away from duplicated user names.

I've done it alright in Insert mode without problems, But the problem now is that an update have to check all the table including itself for the same "User_Name.. which I find problematic due to that i can only found out how to eliminate duplications on other rows, but when a User changes only his "Password" for example and keep the same "User_Name" it won't update since it count the row which has been sent the update.

The current query for update is:

UPDATE [dbo].[Web_Users]
SET [User_Name] = @User_Name,
[Password] = @Password,
[Permission_Level] = @Permission_Level,
[Kod_Cust_Merakez] = @Kod_Cust_Merakez
WHERE [Kod] = @Kod
AND NOT EXISTS (
SELECT *
FROM [dbo].[Web_Users]
WHERE [User_Name] = @User_Name
HAVING COUNT([User_Name]) > 0
)

I know it can and should have been done in other ways, So I need you hand over here :)

Many thanks ahead.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-01-31 : 13:53:08

You can do both conditions in the where clause by using an OR. Note the parenthesist around you original condition)


UPDATE [dbo].[Web_Users]
SET [User_Name] = @User_Name,
[Password] = @Password,
[Permission_Level] = @Permission_Level,
[Kod_Cust_Merakez] = @Kod_Cust_Merakez
WHERE 
(
[Kod] = @Kod
AND NOT EXISTS(Select * from [dbo].Web_Users Where [User_name] = @user_name)
)
	OR
[User_Name] = @User_Name

Perhaps...

Poor planning on your part does not constitute an emergency on my part.

Shadowrack
Starting Member

2 Posts

Posted - 2011-02-01 : 07:28:50

Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources