Update multiple records on a single statement

CREATE TABLE Student

(

StudID		INT IDENTITY(1000,1) CONSTRAINT PK_Student_StudID PRIMARY KEY,

Name		VARCHAR(100),

Location	VARCHAR(15)

)

GO

INSERT Student(Name) VALUES('Aequea')

INSERT Student(Name) VALUES('Salino')

INSERT Student(Name) VALUES('Calcalino')

INSERT Student(Name) VALUES('Setaceo')

INSERT Student(Name) VALUES('Socaprio')

INSERT Student(Name) VALUES('Alumino')

INSERT Student(Name) VALUES('Vitriolic')

GO

CREATE TABLE StudAddress

(

AddressID	INT IDENTITY(1,1) CONSTRAINT PK_StudAddress_AddressID PRIMARY KEY,

StudID		INT CONSTRAINT FK_Student_StudID FOREIGN KEY REFERENCES Student(StudID),

Place		VARCHAR(100),

Pin		BIGINT

)

GO

INSERT StudAddress(StudID,Place,Pin) VALUES(1000,'Chennai',600116)

INSERT StudAddress(StudID,Place,Pin) VALUES(1001,'Goa',700116)

INSERT StudAddress(StudID,Place,Pin) VALUES(1002,'Chennai',800116)

INSERT StudAddress(StudID,Place,Pin) VALUES(1003,'Delhi',810116)

INSERT StudAddress(StudID,Place,Pin) VALUES(1004,'Mumbai',820116)

INSERT StudAddress(StudID,Place,Pin) VALUES(1005,'Kolkatta',830116)

INSERT StudAddress(StudID,Place,Pin) VALUES(1006,'Bangalore',840116)

GO

UPDATE ST SET ST.Location = CASE SA.Place WHEN 'Chennai' THEN 'Local' ELSE 'Remote' END

FROM Student ST WITH(ROWLOCK) 

JOIN StudAddress SA WITH(NOLOCK)

ON (ST.StudID = SA.StudID)

GO

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

sriram