Hi Hugo,

Here is the create table, update and select statements - im not sure
what u mean by ddl, but all the staements you will need to test it are
here.
This is really frustrating me now because it returns a single row with
the select statement. However when you do an update after this
(changing the standIn integer) it returns two rows sometimes. It does
not seem to follow a certain pattern. It doesnt do it if you put all
the updates in together and then do a select. Doing an update and then
trying the select statement sometimes brings back one row and sometimes
two.

Maybe I am missing something really obvious

Thanks for all help.

-------------------------------------

CREATE TABLE [dbo].[tblStaff] (
[StaffNo] [int] IDENTITY (1, 1) NOT NULL ,
[FirstName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LastName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[StandIn] [int] NULL ,

) ON [PRIMARY]
GO


-------------------------------------
Insert into tblstaff
values ('fname1', 'lname2', 2)

Insert into tblstaff
values ('fname1', 'lname2', 1)

Insert into tblstaff
values ('fname1', 'lname2', 1)


----Do individually after here----


UPDATE tblstaff
SET StandIn = 3
WHERE StaffNo = 2

------------------------------------

SELECT T2.FirstName AS StandIn_FirstName, T2.LastName AS
StandIn_LastName
FROM tblStaff AS T1
LEFT OUTER JOIN tblStaff AS T2
ON T1.StaffNo = T2.StandIn
WHERE (T1.StaffNo = 2)


-------------------------------------

UPDATE tblstaff
SET StandIn = 3
WHERE StaffNo = 2



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!