SQL update with inner join table

Question

I'm using SQL Server and I want to update 2 values (ActionNumber and NumberOfPeople) from InterventionsImage where the property NumberOfInterverntion have same value from both ExecuteInterventions and InterventionsImage tables.

Using the logic of SQL update sintax i tried something like this query:

        UPDATE ExecuteInterventions ei INNER JOIN InterventionsImage ii
        ON ei.NumberOfInterverntion = ii.NumberOfIntervention
        SET ii.ActionNumber = 1 and SET ii.NumberOfPeople = 233
        WHERE ei.ID = 153 and ii.ID  = 199687

should update the ActionNumber and NumberOfIntervention values from InterventionsImage table. But of course that does not worked. Can someone figure me out how can i update those values?

duplicate: https://stackoverflow.com/questions/2334712/how-do-i-update-from-a-select-in-sql-server — Tab Alleman, Feb 14 '19 at 14:44

Mukesh Arora · Answer 1 · 2019-02-14T14:43:27.567

0

try below query. this should work...

UPDATE ii SET ii.ActionNumber = 1, ii.NumberOfPeople = 233 From InterventionsImage ii INNER JOIN ExecuteInterventions ei ON ei.NumberOfInterverntion = ii.NumberOfIntervention WHERE ei.ID = 153 and ii.ID = 199687

edited Feb 14 '19 at 14:43

answered Feb 14 '19 at 14:37

Mukesh Arora

1,763
2
8
19

score 0 · Accepted Answer · answered Feb 14 '19 at 14:38

If you are new using Sql server i recommend to do this.

First try to select values that you want to update by using a select clause

Select * 
FROM ExecuteInterventions ei INNER JOIN InterventionsImage ii
ON ei.NumberOfInterverntion = ii.NumberOfIntervention
WHERE ei.ID = 153 and ii.ID  = 199687

Check if those values are the ones that you want to change

Then apply update clause on the following way

Update ii
SET ii.ActionNumber = 1
  , ii.NumberOfPeople = 233
FROM ExecuteInterventions ei INNER JOIN InterventionsImage ii
ON ei.NumberOfInterverntion = ii.NumberOfIntervention
WHERE ei.ID = 153 and ii.ID  = 199687

Copy FROM structure from your query and put an Update clause on it by. On update clause invoke alias of table that you want to update (Ex ii) and apply set clause to change info that you want.

SQL update with inner join table

2 Answers2