June 11, 2007 at 8:39 am
I have 2 tables
1. Emp(empid,name,no)
2. Books(Bookid,empid,name)
Here I want to update empid=2 to empid =4 before that I want to check If empid=2 is in table Books then I have to make sure empid=4 is also in table books else want to raise an error .
June 11, 2007 at 9:32 am
Here's how to raise the error: This assumes that empid 2 and 4 are static, is this the case?
IF NOT EXISTS(Select 1 from Books where empid in (2,4)) BEGIN
RAISERROR('EmployeeId 2 and/or 4 were not found in dbo.Books.',16,1)
... ELSE Update ...
END
or
Update Books Set empid = 4 where empid = 2
IF @@ROWCOUNT = 0 RAISERROR('EmployeeId 2 and/or 4 were not found in dbo.Books.',16,1)
or even further (seperate error for each condition)
DECLARE @ERR VARCHAR(255)
SET @ERR = ''
IF (Select count(*) from Books where empid = 2) = 0
SET @ERR = @ERR+' EmpId 2 was not found in dbo.books.'
IF (Select count(*) from books where empid = 4) = 0
SET @ERR = @ERR+' EmpId 4 was not found in dbo.books.'
IF @ERR = '' BEGIN
SET @ERR = 'The Following errors were encountered before the update.'+@ERR
RAISERROR (@ERR,16,1)
END
ELSE
UPDATE books set empid = 4 where empid = 2
-
June 11, 2007 at 10:31 am
You can also create a foreign key between the 2 emp ids. That way the master key can't be updated if childs exists. You can also add the "on update casacade" option so that you caq update a single table and have all children updated without having to code it.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply