Update multiple records on a single statement

Posted by Murugavelmsc under Sql Server on 2/9/2013 | Points: 10 | Views : 1275 | Status : [Member] | Replies : 4
Hi All

How to Update multiple records on a single statement ?

Thanks,
Murugavel S

Regards,
Murugavel S
murugavel.sadagopan@gmail.com
http://murugavelmsc.blogspot.in/



Responses

Posted by: Rimi1289 on: 2/9/2013 [Member] Starter | Points: 25

Up
0
Down
try this

SELECT * INTO Table2 FROM Table1 WHERE 1=0

Rimi

Murugavelmsc, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Pandians on: 2/9/2013 [Member] [MVP] Silver | Points: 25

Up
0
Down
Not sure what is your requirement...

Update Student.Location column as 'Local' when StudAddress.Place ='Chennai',
'Remote' when StudAddress.Place otherthan 'Chennai'
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 records in one table by referring another table
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

Murugavelmsc, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Kundan64 on: 2/10/2013 [Member] Starter | Points: 25

Up
0
Down
Hi Murugavelmsc,
You can update multiple record by one statement: when you will Not use WHERE condition then update command will work on all the records of the table.

Thanks

Murugavelmsc, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sriramnandha on: 5/12/2013 [Member] Starter | Points: 25

Up
0
Down
update table name set empaddress1='xxx' and empaddress2='yyyy' where empid=101

sriram

Murugavelmsc, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response