Update query with Join SQL Server

Question

I have these two tables

The first image represents ObjectData Table, and the second ColumnsSet Table.

In ObjectData the ColID attribute represents the foreign key of ColumnID in ColumnsSet Table.

I want to update a cell in Data attribute of ObjectData for example number 10 to be 20.

query :

UPDATE ObjectData SET ObjectData.Data = 'Ahmed'
FROM ColumnsSet
INNER JOIN ObjectData ON ColumnsSet.ColumnID = ObjectData.ColID
WHERE ObjectData.ColID = ColumnsSet.ColumnID

What is the correct SQL statement?

Possible duplicate of [SQL update query using joins](https://stackoverflow.com/questions/982919/sql-update-query-using-joins) — Muhammad Saqlain, Nov 27 '18 at 10:33
I have tried this example but it is still not working This is my query "UPDATE ObjectData SET ObjectData.Data = 'Ahmed' FROM ColumnsSet INNER JOIN ObjectData ON ColumnsSet.ColumnID = ObjectData.ColID WHERE ObjectData.ColID = ColumnsSet.ColumnID" — m7md_abdulghani, Nov 27 '18 at 10:35
The same issue, all Data Cells are updated to the new value 'Ahmed'. I only want one cell — m7md_abdulghani, Nov 27 '18 at 10:46
Please add any relevant information, and any things you have tried, in the question not in the comments. — GuidoG, Nov 27 '18 at 10:49
Move your updating table to first UPDATE od SET od.Data = 'Ahmed' FROM ObjectData od INNER JOIN ColumnsSet cs ON cs.ColumnID = od.ColID WHERE od.ColID = cs.ColumnID — Muhammad Saqlain, Nov 27 '18 at 10:50

score 0 · Accepted Answer · answered Nov 27 '18 at 12:25

UPDATE a SET a.Data = 'Ahmed'
FROM ObjectData  a 
INNER JOIN ColumnsSet  ON ColumnsSet.ColumnID = a.ColID

when you are using inner join it selects data which returns join condition true and not null datas there is no need to this 'where ....' but you have to be sure which data will update at first use this code and select all data

SELECT * 
FROM ObjectData  a 
INNER JOIN ColumnsSet  ON ColumnsSet.ColumnID = a.ColID

and then write where clause then change it to update

score 0 · Answer 2 · answered Nov 27 '18 at 12:43

Let me show on example how to update. At first, we should create tables:

CREATE TABLE ObjectData
(
     ID INT,
     CollID INT,
     Data VARCHAR(50)
)

CREATE TABLE ColumnSet
(
     ColumnID INT,
     ColumnName VARCHAR(50)
)

Then insert data:

INSERT INTO ObjectData
(
    ID,
    CollID,
    Data
)
VALUES
(   113, -- ID - int
    1, -- CollID - int
    '1' -- Data - varchar(50)
    )
, (114, 5, '')
, (115, 10015, 'Mohamed')
, (116, 20026, 'Abdulghani')

INSERT INTO ColumnSet
(
    ColumnID,
    ColumnName
)
VALUES
(   1, -- ColumnID - int
    'ID' -- ColumnName - varchar(50)
    )
, (5, 'EmployeeID')
, (10015, 'FirsName')

And the final step is updating where you should write what rows need to be updated by WHERE operator:

UPDATE od
 SET od.Data = 'Hey!'
FROM ObjectData  od 
INNER JOIN ColumnSet cs ON cs.ColumnID =od.CollID
--WHERE OD.DATA IN ('1', '10')
WHERE OD.CollID IN (1, 5)

You can write in WHERE statement any condition to choose your row which will be updated.

--Check our update statements:
SELECT
*
FROM ObjectData  od 
INNER JOIN #ColumnSet cs ON cs.ColumnID =od.CollID
WHERE OD.DATA IN ('1', '10')

Update query with Join SQL Server

2 Answers2