-
Notifications
You must be signed in to change notification settings - Fork 213
Expand file tree
/
Copy pathupdate_from_select.sql
More file actions
164 lines (139 loc) · 2.91 KB
/
Copy pathupdate_from_select.sql
File metadata and controls
164 lines (139 loc) · 2.91 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
/*
Sample data and scripts for the "How to Update from Select in SQL" post.
*/
/*
Oracle
*/
CREATE TABLE person (
person_id NUMBER,
first_name VARCHAR2(100),
account_number NUMBER
);
CREATE TABLE account (
account_id NUMBER,
account_number NUMBER,
person_id NUMBER
);
INSERT INTO person (person_id, first_name)
VALUES (1, 'John');
INSERT INTO person (person_id, first_name)
VALUES (2, 'Sarah');
INSERT INTO person (person_id, first_name)
VALUES (3, 'Mark');
INSERT INTO account (account_id, account_number, person_id)
VALUES (1, 100298, 2);
INSERT INTO account (account_id, account_number, person_id)
VALUES (2, 103557, 3);
INSERT INTO account (account_id, account_number, person_id)
VALUES (3, 108956, 1);
INSERT INTO account (account_id, account_number, person_id)
VALUES (4, 109703, 4);
/*
SQL Server, MySQL
*/
CREATE TABLE person (
person_id INT,
first_name VARCHAR(100),
account_number INT
);
CREATE TABLE account (
account_id INT,
account_number INT,
person_id INT
);
INSERT INTO person (person_id, first_name) VALUES
(1, 'John'),
(2, 'Sarah'),
(3, 'Mark');
INSERT INTO account (account_id, account_number, person_id) VALUES
(1, 100298, 2),
(2, 103557, 3),
(3, 108956, 1),
(4, 109703, 4);
/*
Postgres
*/
CREATE TABLE person (
person_id INTEGER,
first_name CHARACTER VARYING(100),
account_number INTEGER
);
CREATE TABLE account (
account_id INTEGER,
account_number INTEGER,
person_id INTEGER
);
INSERT INTO person (person_id, first_name) VALUES
(1, 'John'),
(2, 'Sarah'),
(3, 'Mark');
INSERT INTO account (account_id, account_number, person_id) VALUES
(1, 100298, 2),
(2, 103557, 3),
(3, 108956, 1),
(4, 109703, 4);
/*
1 - Update with From Join
*/
UPDATE p
SET p.account_number = a.account_number
FROM person p
INNER JOIN account a
ON p.person_id = a.person_id;
/*
2 - Update with From Second Table
*/
UPDATE person
SET account_number = account.account_number
FROM account
WHERE person.person_id = account.person_id;
/*
3 – Update with Join in Update Clause
*/
UPDATE person
INNER JOIN account
ON person.person_id = account.person_id
SET person.account_number = account.account_number;
/*
4 – Update with Inline View
*/
UPDATE (
SELECT
p.person_id,
p.account_number AS person_account_number,
a.account_number
FROM person p
INNER JOIN account a ON p.person_id = a.person_id
) sub
SET sub.person_account_number = sub.account_number;
/*
5 – Update with Subquery
*/
UPDATE person
SET account_number = (
SELECT account_number
FROM account
WHERE account.person_id = person.person_id
);
/*
6 – Update using WITH Clause
*/
WITH subquery AS (
SELECT
account_id,
account_number,
person_id
FROM account
)
UPDATE person
SET account_number = subquery.account_number
FROM subquery
WHERE person.person_id = subquery.person_id;
/*
7 – Merge Statement
*/
MERGE INTO person p
USING account a
ON (p.person_id = a.person_id)
WHEN MATCHED THEN
UPDATE SET p.account_number = a.account_number;