• Marek Vavrovic

Update table using join statement

Examples of SQL Update Join


RIGHT JOIN

LEFT JOIN

INNER JOIN

WHERE clause

CASE statement

Computed Columns




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.



15 views0 comments