Conditional Update

Rajnilari2015
Posted by Rajnilari2015 under Sql Server category on | Points: 40 | Views : 153
Suppose we have two tables

A) TableA

ID	Amount
1 2000
2 5000
3 12000
4 21000
5 52000
6 16000


B) TableB
ID	Amount
1 34000
2 78000
3 90000
4 11000
5 7000
6 9000


We need to Update those records of TableA From TableB whose ID is Divisible by 2. The below TSQL code will do so

CREATE TABLE tableA
(
ID INT IDENTITY(1,1),
Amount INT
)

CREATE TABLE tableB
(
ID INT IDENTITY(1,1),
Amount INT
)


INSERT INTO tableA VALUES(2000)
INSERT INTO tableA VALUES(5000)
INSERT INTO tableA VALUES(12000)
INSERT INTO tableA VALUES(21000)
INSERT INTO tableA VALUES(52000)
INSERT INTO tableA VALUES(16000)

INSERT INTO tableB VALUES(34000)
INSERT INTO tableB VALUES(78000)
INSERT INTO tableB VALUES(90000)
INSERT INTO tableB VALUES(11000)
INSERT INTO tableB VALUES(7000)
INSERT INTO tableB VALUES(9000)

--Update those records of TableA From TableB whose ID is Divisible by 2
UPDATE ta
SET
ta.Amount=tb.Amount
FROM tableA ta
JOIN tableB tb ON ta.ID=tb.ID AND ta.ID %2 = 0

--Project the records of TableA
SELECT *
FROM tableA

--Clean Up
DROP TABLE tableA,tableB


/*
ID	Amount
1 2000
2 78000
3 12000
4 11000
5 52000
6 9000

*/

Comments or Responses

Login to post response