CODEWITHSUNDEEP
sqlsql-serversql-server-2017
Ivan-Mark Debono
Ivan-Mark Debono

Reputation: 16330

How to update a table by using a join of two tables?

I have a table with these columns:

Id int, Reference varchar(50), FileId int

The 'Reference' column always ends with a "D" or a "C". The FileId needs to be updated. On update I have a table varilable:

DECLARE @fileIds TABLE (
  Id int NOT NULL,
  [Type] varchar(1) NOT NULL
);

This table variable always has a maximum of 2 records and at least 1 record. The Type column has a value of "D" or "C"

Now I need to update my table by setting the FileId to match the corresponding file id. Is this the correct way to do the update:

UPDATE myTable 
SET 
    FileId = f.Id
FROM
    myTable
    INNER JOIN @fileIds f ON RIGHT(s.Reference, 1) = p.[Type]

Upvotes: 0

Views: 44

Answers (2)

Hemang A
Hemang A

Reputation: 1012

I think missed alias but you have used two different alias. So, "Reference" and "Type" column is which tables to belongs because use others alias.

Update using join syntax.

UPDATE
    A
SET 
    A.FileId = B.Id
FROM 
    tableA AS A
INNER JOIN 
    tableb AS B ON A.Id = B.Id

Upvotes: 1

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31991

you missed alias name of mytable

UPDATE s 
SET 
    FileId = f.Id
FROM
    myTable s
    INNER JOIN @fileIds f ON RIGHT(s.Reference, 1) = f.[Type]

Upvotes: 0

Related Questions