How to write update procedure for predefined values and non-predefined values

Question

1.00/5 (1 vote)

See more:

i have one table with column name account names.i have some predefined values for account like bankAC,loanAc,salesAc etc etc. i am updating my account table with account names. and this predefined accounts are not allowed to delete.only user created accounts are allowed to delete. and i have id with scope identity.

i want user created group name(Account group names)should be updated and predefined group names should not be updated.

What I have tried:

i have this update procedure

BEGIN

--SET NOCOUNT ON;
UPDATE tbl_AccountGroup SET Group_name = @Group_name, Comments = @Comments, chbox = @chbox, Under_Group= @Under_Group
WHERE Group_name = @Group_name;
END

Posted 15-Mar-16 21:56pm

Member 12385326

Updated 16-Mar-16 4:15am

Add a Solution

Comments

OriginalGriff 16-Mar-16 4:20am

Why are you setting the Group_name to the value that you use for selecting the records for update?
Think about it: if it matches the WHERE clause, it already contains the value you are setting it to...

For the rest of your question I don't understand what problem you are having, or what help you need.
Perhaps an example of your data before and after the operation you want would help?
Use the "Improve question" widget to edit your question and provide better information.

CHill60 16-Mar-16 5:51am

OP is trying to respond via a solution
"because i want to update group name also but with the same id. i have id as primary key with scope identity.but if i use id then it is not updating anything" (sic)

CHill60 16-Mar-16 5:54am

Which code is it ... the sample you have here (which is silly - see OriginalGriff's comment) or the sample you gave on How to update all the columns in the table having scope identity id as primary key[^].
Delete one of your posts and make sure the other one has the correct code sample in it. Use the Improve question link to do this

2 solutions

Solution 3

I get the impression your update won't work because a constraint is blocking it, you could try the following (make a backup first):

SQL

ALTER TABLE tbl_AccountGroup NOCHECK CONSTRAINT ALL
-- Do your thing, e.g.
DROP TABLE tbl_AccountGroup
ALTER TABLE tbl_AccountGroup CHECK CONSTRAINT ALL

Posted 16-Mar-16 4:15am

RickZeeland

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

ZurdoDev · Accepted Answer · 2016-03-16T04:03:00

Solution 2

The way you have described your update does not make any sense, but if you do not want to update certain records then you need to exclude them in a WHERE clause.

SQL

UPDATE table1 
SET field1 = @field1
WHERE group_name NOT IN('one', 'two', 'three')

I would also suggest adding a flag column to that table, a bit would work, and 1 means it is a system record and cannot be touched and 0 means it was created by user. It will make finding them much easier.

Posted 16-Mar-16 4:03am

ZurdoDev

Comments

Member 12385326 17-Mar-16 2:29am

UPDATE table1
SET field1 = @field1
WHERE group_name NOT IN('one', 'two', 'three') this query is updating all records which are not in where clause with same value.

ZurdoDev 17-Mar-16 7:04am

Right, change it to what you need.

Member 12385326 17-Mar-16 7:24am

but it is updating each record not specific record.enen if i changed where clause according to my need.

ZurdoDev 17-Mar-16 8:06am

Can you clearly explain what you need to do?

Member 12385326 18-Mar-16 2:25am

i want to update my user defined record only not system record. but if i use above query then it is updatating every record which are not in where condition.

ZurdoDev 18-Mar-16 7:03am

You still are not being clear. If you don't want to update something, then exclude it using your WHERE clause. This is very easy to do so I'm not sure where you are stuck.

Member 12385326 18-Mar-16 7:26am

i got the solution by setting flag variable.
this is my insert and update procedure for my accountgroup table

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
create PROCEDURE [dbo].[spr_AccountGroup]
@Group_Name varchar(50)=null,

@Chbox_Val varchar(50)=null,

@UnderGroup varchar(50)=null,
@Comments varchar(50)=null,
@flag varchar(50)=null,

@abc varchar(50)=null

AS
BEGIN
IF @abc='insert'
BEGIN
--SET IDENTITY_INSERT tbl_AccountGroup ON
set nocount on;
DECLARE @Group_id int

if( ((@Group_name='Capital Account'or @Group_name='Loan' or @Group_name='Current Liabilities' or @Group_Name='Current Assets')and (@UnderGroup='Capital Account'or @UnderGroup='Loan' or @UnderGroup='Current Liabilities' or @underGroup='Current Assets'))or (@Group_name='Capital Account'or @Group_name='Loan' or @Group_name='Current Liabilities' or @Group_Name='Current Assets' ))

begin
insert into tbl_AccountGroup(Group_Name,Chbox_Val ,UnderGroup,Comments,flag)
values(@Group_Name,@Chbox_Val ,@UnderGroup,@Comments,'1')
select @Group_Name
set @Group_id= SCOPE_IDENTITY();

end
else

insert into tbl_AccountGroup(Group_Name,Chbox_Val ,UnderGroup,Comments,flag)
values(@Group_Name,@Chbox_Val ,@UnderGroup,@Comments,'0')
select @Group_Name
set @Group_id= SCOPE_IDENTITY();

end

if @abc='update'

begin

begin

update tbl_AccountGroup set Chbox_Val=@Chbox_Val ,UnderGroup=@UnderGroup,Comments=@Comments
where Group_Name=@Group_Name and flag='0'

end

end
END