update 370 records on a table while inner joined to another for a where statement

Question

Ok, so im pretty new to SQL and im trying to write a query to replace the number 2 with the number 4 if the officeid=10 so what I have at the moment is the following. One table is called Carer_Supervisions and this is linked to another table VIA a secondary key and the officeid is on another table called carer_information.

SELECT Carer_Supervisions.SupervisionID
    ,Carer_Supervisions.RegionID
    ,Carer_Supervisions.Date_Created
    ,Carer_Supervisions.Created_By
    ,Carer_Supervisions.Complete
    ,Carer_Supervisions.Completed_Date
    ,Carer_Supervisions.Completed_By
    ,Carer_Supervisions.CarerID
    ,Carer_Supervisions.Visit_Date
    ,Carer_Supervisions.SSW
    ,Carer_Supervisions.Location
    ,Carer_Supervisions.Present
    ,Carer_Supervisions.Child1
    ,Carer_Supervisions.Child2
    ,Carer_Supervisions.Child3
    ,Carer_Supervisions.Child4
    ,Carer_Supervisions.Child5
    ,Carer_Supervisions.Child6
    ,Carer_Supervisions.Child7
    ,Carer_Supervisions.Child8
    ,Carer_Supervisions.Caring_for_Children
    ,Carer_Supervisions.Education
    ,Carer_Supervisions.Working_Team
    ,Carer_Supervisions.Training_and_Development
    ,Carer_Supervisions.Environment
    ,Carer_Supervisions.Concerns
    ,Carer_Supervisions.Personal
    ,Carer_Supervisions.Agency
    ,Carer_Supervisions.Day_Care
    ,Carer_Supervisions.Manager
    ,Carer_Supervisions.Carer_Signed
    ,Carer_Supervisions.Cancelled
    ,Carer_Supervisions.Cancel_Reason
    ,Carer_Supervisions.Signed_Sent
    ,Carer_Information.OfficeID
FROM Carer_Supervisions
INNER JOIN Carer_Information ON Carer_Supervisions.CarerID = Carer_Information.CarerID

UPDATE Carer_supervisions
SET RegionID = REPLACE(RegionID, '2', '4')
WHERE (Carer_Information.OfficeID = 10)

is this right or need amending?

Can help http://stackoverflow.com/questions/1262786/mysql-update-query-based-on-select-query or http://stackoverflow.com/questions/2334712/how-to-update-from-a-select-in-sql-server — b2ok, Apr 05 '17 at 11:16

Shakeer Mirza · Accepted Answer · 2017-04-06T05:16:15.077

0

This way you can update.

UPDATE CS SET RegionID= REPLACE(RegionID, '2', '4')
FROM Carer_Supervisions CS 
INNER JOIN Carer_Information CI ON CS.CarerID = CI.CarerID
WHERE CI.OfficeID = 10

Try this one:

UPDATE Carer_Supervisions SET Carer_Supervisions.RegionID= REPLACE(CS.RegionID, '2', '4')
FROM Carer_Supervisions CS 
INNER JOIN Carer_Information CI ON CS.CarerID = CI.CarerID
WHERE CI.OfficeID = 10

edited Apr 06 '17 at 05:16

answered Apr 05 '17 at 11:18

Shakeer Mirza

5,054
2
18
41

tried this got the following "Column or expression 'RegionID' cannot be updated" Error Source: .Net sqlclient data provider Error Message: Invalid object name 'CS' – Ben Nickless Apr 05 '17 at 14:16

update 370 records on a table while inner joined to another for a where statement

1 Answers1