-
Notifications
You must be signed in to change notification settings - Fork 0
/
update_join.sql
60 lines (60 loc) · 1.53 KB
/
update_join.sql
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
-- table yaratish
CREATE TABLE product_segment (
id SERIAL PRIMARY KEY,
segment VARCHAR NOT NULL,
discount NUMERIC (4, 2)
);
-- tablega ma'lumot yozish
INSERT INTO
product_segment (segment, discount)
VALUES
('Grand Luxury', 0.05),
('Luxury', 0.06),
('Mass', 0.1);
-- table yaratish
CREATE TABLE product(
id SERIAL PRIMARY KEY,
name VARCHAR NOT NULL,
price NUMERIC(10,2),
net_price NUMERIC(10,2),
segment_id INT NOT NULL,
FOREIGN KEY(segment_id) REFERENCES product_segment(id)
);
-- tablega ma'lumot yozish
INSERT INTO
product (name, price, segment_id)
VALUES
('diam', 804.89, 1),
('vestibulum aliquet', 228.55, 3),
('lacinia erat', 366.45, 2),
('scelerisque quam turpis', 145.33, 3),
('justo lacinia', 551.77, 2),
('ultrices mattis odio', 261.58, 3),
('hendrerit', 519.62, 2),
('in hac habitasse', 843.31, 1),
('orci eget orci', 254.18, 3),
('pellentesque', 427.78, 2),
('sit amet nunc', 936.29, 1),
('sed vestibulum', 910.34, 1),
('turpis eget', 208.33, 3),
('cursus vestibulum', 985.45, 1),
('orci nullam', 841.26, 1),
('est quam pharetra', 896.38, 1),
('posuere', 575.74, 2),
('ligula', 530.64, 2),
('convallis', 892.43, 1),
('nulla elit ac', 161.71, 3);
-- UPDATE JOIN
UPDATE product
SET net_price = price - price * discount
FROM product_segment
WHERE product.segment_id = product_segment.id;
--
UPDATE
product p
SET
net_price = price - price * discount
FROM
product_segment s
WHERE
p.segment_id = s.id;