Solved

# Update procedure

Posted on 2011-04-29
286 Views
I have the following tables and data. How can I create SP to achieve the following result?

Table1
ReqId(PK)   desc               status(bit)
1                    some text      0
2                    some text      0

Table2
Id(PK)      ReqId(FK)      shopId      appStatus(bit)      sequence (int)
1      1            W      0            1
2      1            M      0            2
3      1            C      0            3
4      2            W      0            1

user provides ReqId = 1 and Id=2

I want to update the appStatus to 1 for the id=2.
Before updating the table I want make surethe following
1. Table1.status is not 1 for ReqId 1
2. if a record has less 'sequence' for ReqId 1 then I want to make sure that the less 'sequence' record's appStatus is 1. allow to update appStatus to 1 only after the previous record's (less sequence) being updated.

ayha
0
Question by:ayha1999

LVL 39

Assisted Solution

Say user provides @ID and @reqId
If Exists ( Select * from Table1 Where status <> 1 and ReqId = @ReqId)
Begin

If Exists ( select * from Table2 where Id =(@id-1) )
begin
Update Table2
set appStatus = 1
where ReqId = 1 and Id= 2  and appStatus =1
end
else
begin
Update Table2
set appStatus = 1
where ReqId = 1 and Id= 2
end

End
0

LVL 7

Author Comment

could u pls explain "If Exists ( select * from Table2 where Id =(@id-1) )"?
0

LVL 5

Accepted Solution

Hi,

If user provides Id, then no need to provide ReqId

Using ID,
1. get the ReqID.
2. Update Table2 only if respective record in Table1.status is not equal to 1.
3. Update Table2.Status whose sequce of respective ReqId is less than current ID's Status (I think, here we should check whether lesser sequence's appStatus is 1 otherwise we should not update current record, but you have asked to update appStatus of lesser sequence along with current record, if that is the case, we can blindly update all the appStatus =1 for sequence lesser or equal to current sequcne instead of lesser sequence. Anyhow, following is my sample script as per your requirement)
4. Then finally you can update Table2.Status for ID
``````CREATE PROCEDURE test
@Id int
AS
BEGIN
SET NOCOUNT ON;

IF EXISTS(SELECT Id FROM Table1 WHERE Id = @ID)
BEGIN
DECLARE @ReqID int, @Sequence int;
SELECT @ReqID = T2.ReqId, @Sequence = T2.Sequence FROM Table1 T1 INNER JOIN Table2 T2 on T1.ReqId = T2.ReqId WHERE T1.Id = @ID AND T1.appStatus <> 1
IF @ReqID IS NULL
PRINT 'No need to update / invalid data'
ELSE
BEGIN
UPDATE Table2 SET appStatus = 1 WHERE Sequence <= @Sequence AND ReqID = @ReqID
END
END
END
GO
``````
0

LVL 7

Author Comment

"but you have asked to update appStatus of lesser sequence along with current record" this is my mistake. no need to upadate the current record if thtat's the case.
0

LVL 7

Author Comment

also please incldue this condition "Table1.status is not 1 for ReqId 1"
0

LVL 7

Author Comment

here is 'sequence' no is used to make sure the order. a record with sequence 2 cannot be updated if the record with sequence is not updated (appstatus)
0

LVL 5

Expert Comment

Ok, I have already included the condition "Table1.status is not 1 for ReqId 1"  as
T1.appStatus <> 1
in the line 10.

If you dont want to update current record then update the line 15 as :
UPDATE Table2 SET appStatus = 1 WHERE Sequence < @Sequence AND ReqID = @ReqID  AND Id <> @Id
0

LVL 39

Expert Comment

If Exists ( select * from Table2 where Id =(@id-1) )

this is to check the lesser sequance
0

LVL 5

Expert Comment

Earlier you asked to update appStatus as 1 for lesser sequence numbers. Now what you are asking, I cant able to understand "a record with sequence 2 cannot be updated if the record with sequence is not updated (appstatus) ". I understand that sequence number is to store the order, other than that I can't understand your correct requirement.
0

LVL 7

Author Comment

If Exists ( select * from Table2 where Id =(@id-1) )

this is not checking the sequence no.

before updating the record (with provided id) it has check that if any record exists with status 0 for provided reqid. if so no need to update else update the record.

i will my actual data I tried.

``````ALTER PROCEDURE dbo.updateStatus
(@ReqId int, @AssignId int)
AS
If Exists (Select * from CustService Where pickstatus <> 1 and CustService.RequestId = @ReqId)
Begin

If Exists ( select * from Assignment where AssignId =(@AssignId-1) and Assignment.RequestId=@ReqId)
begin
Update Assignment
set Status = 1
where AssignId= @AssignId
end

End
RETURN
``````
0

LVL 7

Author Comment

id = AssignId
reqId = RequestId
Table1 = custService
Table2 = Assignment
status=pickstatus
appstatus=status
0

LVL 5

Expert Comment

why are you using
AssignId =(@AssignId-1)

AssignId is a auto generated Id know, it is not a sequence number to check lesser sequence.
0

LVL 7

Author Comment

yes. but I was just trying his solution first. I think you are confused with table structure. I make some corrections in the your code and executed. I tried to update the second record (seq. 2) then it just updated the status without checking the lesser seq. record. the second record should not get udpated bec. a lesser seq. recrod is there with status 0.
0

LVL 7

Author Closing Comment

The solution isnot complete.
0

## Featured Post

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!