Examples of SQL Update Join
create table MyTarget
(id int Identity(1,1),
TargetName varchar(10))
create table MySource
(Id int Identity(1,1) ,
SourceName varchar(10)
)
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.
RIGHT JOIN
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.
LEFT JOIN
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
UPDATE MyTarget
SET MyTarget.TargetName = MySource.SourceName
FROM MyTarget
LEFT JOIN MySource ON MySource.Id = MyTarget.id
SAVE TRAN DeleteNulls
IF EXISTS(SELECT TargetName FROM MyTarget WHERE TargetName IS NULL)
BEGIN
DELETE FROM MyTarget WHERE TargetName IS NULL
END
ELSE
BEGIN
ROLLBACK TRAN DeleteNulls
END
COMMIT TRAN MYTARGET
INNER JOIN
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.
BEGIN TRAN
UPDATE FactTransactions
SET
FactTransactions.FName = Transactions.FName,
FactTransactions.Amount = Transactions.Amount,
FactTransactions.DOB = Transactions.DOB
FROM FactTransactions
INNER JOIN Transactions
ON FactTransactions.TransactionKey = Transactions.Id
COMMIT TRAN
WHERE clause
I want to update FactTransaction table ( table on the bottom) value 100 replace with600 and 300 replace with 800
BEGIN TRAN
UPDATE FactTransactions
SET FactTransactions.Amount = Transactions.Amount
FROM FactTransactions
INNER JOIN Transactions ON FactTransactions.TransactionKey = Transactions.ID
WHERE FactTransactions.TransactionKey IN (1,2)
COMMIT TRAN
CASE statement
If column xx in FactTransactions is Bad, multiply Amount value in Transaction by 1.25
BEGIN TRAN
UPDATE Transactions
SET Transactions.Amount =
Case FactTransactions.xx When 'Bad' THEN Transactions.Amount * 1.25
ELSE Transactions.Amount END
FROM Transactions
INNER JOIN FactTransactions ON Transactions.Id = FactTransactions.TransactionKey
COMMIT TRAN
Computed Columns
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),
Amount int,
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.
