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
 General SQL Server Forums
 New to SQL Server Programming
 update data by condition

Author  Topic 

eugz
Posting Yak Master

210 Posts
Posted - 2012-02-03 : 10:55:40
Hi All.
I have update update procedure

ALTER Procedure [sp_UpdateProvider]
	(
		@Class_Id bigint
		,@User_Id bigint
		,@Provider_Id bigint
		,@Primary bit
	)
as

UPDATE Provider
SET		User_Id = @User_Id
		,Provider_Id = @Provider_Id
		,Primary = @Primary
WHERE Class_Id = @Class_Id


I need before update table check if Primary field is NULL or FALSE just update table. If Primary field TRUE set that field to FALSE and then update table. How it to do?

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-02-03 : 11:08:51
are you looking at something like below?

ALTER Procedure [sp_UpdateProvider]
	(
		@Class_Id bigint
		,@User_Id bigint
		,@Provider_Id bigint
		,@Primary bit
	)
as

UPDATE Provider
SET		User_Id = @User_Id
		,Provider_Id = @Provider_Id
		,Primary = CASE WHEN Primary IS NULL OR Primary='FALSE' THEN @Primary ELSE 'False' END
WHERE Class_Id = @Class_Id





------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

eugz
Posting Yak Master

210 Posts
Posted - 2012-02-03 : 11:30:01
Hi visach16. Thanks for replay.

I edit my first post for condition possible in my project.

Primary = CASE WHEN Primary IS NULL or 'False' THEN @Primary ELSE 'False' END

But, for instance, I have Primary = 'True'. The result execution of this statement Primary = 'False'. By that step we just reset Primary field. After that I need to update table again and get new value for Primary field. How that to do?

Thanks.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-02-03 : 11:35:35
hmm..what does that mean?

please explain with some sample data. I cant make out what exactly you're looking at by your explanation above

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

eugz
Posting Yak Master

210 Posts
Posted - 2012-02-03 : 11:47:30
I would like before update table set Primary field to FALSE and then update table to new value. Because in my project same User_Id must has only one Primary equal TRUE. That why I would like before update table reset Primary field to FALSE and than update it with new value of Primary field.

For example, I need to have like
Class_Id | user_id | Primary
---------+---------+---------
1 | 1 | True
2 | 1 | False
3 | 1 | False
4 | 2 | True
5 | 2 | False

Thanks.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-02-03 : 11:53:08
so you want always last record to have Primary='True'? and old ones you close as Primary='False'? What columns you use for determining unqiue group?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-02-03 : 11:58:38
how do you determine which one should have true? do you've any other unique valued column in table?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

eugz
Posting Yak Master

210 Posts
Posted - 2012-02-03 : 11:58:58
Sorry I just add example to my previous post for detail view what I would like to have.

Thanks.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-02-03 : 12:00:47
quote:
Originally posted by eugz

Sorry I just add example to my previous post for detail view what I would like to have.

Thanks.

still its not answering my question

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

eugz
Posting Yak Master

210 Posts
Posted - 2012-02-03 : 12:06:47
The field Class_Id is unique in Provider table.

Thanks.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-02-03 : 12:08:37
sorry that does make much sense. how will earliest Clas_id get primary=true? is it auto decrementing field?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

eugz
Posting Yak Master

210 Posts
Posted - 2012-02-03 : 12:11:58
Yes, it auto increment by 1.

Thanks.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-02-03 : 12:15:49
quote:
Originally posted by eugz

Yes, it auto increment by 1.

Thanks.

then how will earliest record get primary = 'true'
or is it like always value passed will be true in procedure for @Primary but we need to make it false before updation except first instance?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

eugz
Posting Yak Master

210 Posts
Posted - 2012-02-03 : 12:30:15
I have the front end form to insert and update record for Provider table. And if user open form with record where Primary field is NULL so by click save button record will added to table with value TRUE or FALSE. If user will open form with record where Primary field has value TRUE I would like when user will click save button set Primary field to FALSE and than update Provider table with new Primary field value.

Thanks.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-02-03 : 12:34:17
then shouldnt this suffice?

ALTER Procedure [sp_UpdateProvider]
	(
		@Class_Id bigint
		,@User_Id bigint
		,@Provider_Id bigint
		,@Primary bit
	)
as

UPDATE Provider
SET		User_Id = @User_Id
		,Provider_Id = @Provider_Id
		,Primary = CASE WHEN Primary 'TRUE' THEN 'False' ELSE @Primary END
WHERE Class_Id = @Class_Id



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

eugz
Posting Yak Master

210 Posts
Posted - 2012-02-03 : 12:51:57
That code only for according record. I need, for instance, user will open form with record where User_Id = 1 and Primary='True'. In that case when user will click Save button all records where User_Id = 1 will set Primary to value 'False' and then update Provider table.

Thanks.
Go to Top of Page

eugz
Posting Yak Master

210 Posts
Posted - 2012-02-03 : 14:56:49
The problem is solved.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-02-03 : 15:55:35
quote:
Originally posted by eugz

The problem is solved.

how?
can you post the solution?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site