CODEWITHSUNDEEP
sql-servert-sql
PJW
PJW

Reputation: 5397

SQL Update One Table with Value from Another Table

I have the following SQL statement -

SELECT e.*, i.CaseNo, c.Claimant, c.Defendant, c.ClientID
FROM tblExpenses AS e
INNER JOIN tblInvoices AS i ON e.InvNo = i.InvNo
INNER JOIN tblCases AS c ON i.CaseNo = c.CaseNo

which demonstrates the link between three tables.

tblInvoices has a primary key [InvNo] which is also a foreign key in tblExpenses thus linking each expense with the relevant invoice.

tblCases has a primary key [CaseNo] which is also a foreign key in tblInvoices, thereby linking each invoice to a particular case.

Finally each case in tblCases has a column called [ClientID] which identifies the client whom the invoice was sent to.

Now then, tblExpenses also has a foreign key [ClientID] but at present the column is not populated. What I want to do, is use the above links to populate [ClientID] in tblExpenses, with the [ClientID] from tblCases, via the aforementioned links.

However I am unclear how to rewrite my SELECT query in order to carry out this population of the [ClientID] column in tblExpenses.

Can anyone please assist?

Upvotes: 1

Views: 62

Answers (1)

Azmi Kamis
Azmi Kamis

Reputation: 901

You already have the sufficient select query to get the required information. You just need to convert it to an update query.

UPDATE e SET e.ClientID=c.ClientID
FROM tblExpenses AS e
INNER JOIN tblInvoices AS i ON e.InvNo = i.InvNo
INNER JOIN tblCases AS c ON i.CaseNo = c.CaseNo

Upvotes: 1

Related Questions