Update table using join statement
Examples of SQL Update Join
create table MyTarget
(id int Identity(1,1),
create table MySource
(Id int Identity(1,1) ,
insert into MyTarget(TargetName) VALUES ('John'),('Steve'),('Maria'),('Boris')
insert into MySource(SourceName) VALUES ('John'),('Steve'),('Lola')
Select * from MyTarget
Select * from MySource
I have two tables. I want to update MyTarget with matching data from MySource. I do not want to perform any insert or delete or anything else.
The RIGHT JOIN keyword returns all records from the right table (MyTarget), and the matching records from the left table (MySource). The result is 0 records from the left side, if there is no match.
If I want to update MyTarget table using data from MySource, I use LEFT JOIN. The LEFT JOIN keyword returns all records from the left table (MySource), and the matching records from the right table (MyTarget). The result is 0 records from the right side, if there is no match.
Let's say I want an exact match, so no matching rows will be deleted .
BEGIN TRAN MYTARGET
SET MyTarget.TargetName = MySource.SourceName
LEFT JOIN MySource ON MySource.Id = MyTarget.id
SAVE TRAN DeleteNulls
IF EXISTS(SELECT TargetName FROM MyTarget WHERE TargetName IS NULL)
DELETE FROM MyTarget WHERE TargetName IS NULL
ROLLBACK TRAN DeleteNulls
COMMIT TRAN MYTARGET
Updating multiple columns using INNER JOIN. The INNER JOIN selects records that have matching values in both tables.
I want to update FactTransactions table, replace Lola for Maria, Amount 0 for 500 and DOB 1940-05-01 for 1980-05-01
I have equal number of rows, can use LEFT JOIN or INNER JOIN in this case.
FactTransactions.FName = Transactions.FName,
FactTransactions.Amount = Transactions.Amount,
FactTransactions.DOB = Transactions.DOB
INNER JOIN Transactions
ON FactTransactions.TransactionKey = Transactions.Id
I want to update FactTransaction table ( table on the bottom) value 100 replace with600 and 300 replace with 800
SET FactTransactions.Amount = Transactions.Amount
INNER JOIN Transactions ON FactTransactions.TransactionKey = Transactions.ID
WHERE FactTransactions.TransactionKey IN (1,2)
If column xx in FactTransactions is Bad, multiply Amount value in Transaction by 1.25
SET Transactions.Amount =
Case FactTransactions.xx When 'Bad' THEN Transactions.Amount * 1.25
ELSE Transactions.Amount END
INNER JOIN FactTransactions ON Transactions.Id = FactTransactions.TransactionKey
Computed Columns in SQL Server with Persisted Values
If Persisted property is off then calculated column will be just a virtual column. No data for this column will be stored on disk and values will be calculated every time when referenced in a script. If this property is set active then data of computed column will be stored on disk.
Any update in referenced column will be synchronized automatically in computed column if it is Persisted.
Along with some other conditions Persisted is required to create an index on the computed column.
You can not reference columns from other tables for a computed column expression directly.
You can not apply insert or update statements on computed columns.
If you are combining operators of two different data types in your expression then operator of lower precedence will be converted to that of higher precedence. If implicit conversion is not possible then error will be generated.
A subquery can not be used as an expression for creating a computed column.
Computed columns can be used in SELECT lists, WHERE or ORDER BY clauses and as regular expressions , but to use a computed column as CHECK, FOREIGN KEY or NOT NULL constraints you have to set it to Persisted.
To use a computed column as Primary or Unique Key constraint it should be defined by a deterministic expression and data type of computed column expression should be indexable.
Examples of computed columns
create table Sales
id int primary key identity(1,1),
AmountDescription as CAST( case
when Amount < 100 then 'Bronze'
when Amount >=100 and Amount <300 then 'Silver'
else 'Gold' end as varchar(50)) persisted
ALTER TABLE Sales ADD Commissions
as ( Amount *0.15) persisted
It doesn't matter if the column has been mark as Persisted or not, there is no way how to update it with an UPDATE statement. You have to change the Computed Column Specification in Column Properties.