MySQL 5.7

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE `temp_sb` (
  `ticker` tinyint(4) unsigned NOT NULL,
  `contract` tinyint(3) unsigned NOT NULL,
  `dt` datetime NOT NULL,
  `price` mediumint(9) unsigned NOT NULL,
  `p_open` tinyint(1) unsigned NOT NULL,
  `p_close` tinyint(1) unsigned NOT NULL,
  `volume` smallint(6) unsigned NOT NULL,
  `trades` smallint(6) unsigned NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO temp_sb VALUES
(9,50,'2019-08-01 07:27:00.0',14186,0,0,106,13),
(9,50,'2019-08-01 07:27:00.0',14185,0,0,112,87),
(9,50,'2019-08-01 07:27:00.0',14184,0,0,210,77),
(9,50,'2019-08-01 07:27:00.0',14183,0,0,89,55),
(9,50,'2019-08-01 07:27:00.0',14182,0,0,343,138),
(9,50,'2019-08-01 07:27:00.0',14181,0,0,95,63),
(9,50,'2019-08-01 07:28:00.0',14187,0,0,3,3),
(9,50,'2019-08-01 07:28:00.0',14186,0,0,16,13),
(9,50,'2019-08-01 07:28:00.0',14185,0,0,71,60),
(9,50,'2019-08-01 07:28:00.0',14184,0,0,93,79),
(9,50,'2019-08-01 07:28:00.0',14183,0,0,37,31),
(9,50,'2019-08-01 07:28:00.0',14182,0,0,52,43),
(9,50,'2019-08-01 07:28:00.0',14181,0,0,247,194),
(9,50,'2019-08-01 07:28:00.0',14180,0,0,415,140),
(9,50,'2019-08-01 07:28:00.0',14179,0,0,3,3),
(9,50,'2019-08-01 07:29:00.0',14185,0,0,133,94),
(9,50,'2019-08-01 07:29:00.0',14184,0,0,96,73),
(9,50,'2019-08-01 07:29:00.0',14183,0,0,73,48),
(9,50,'2019-08-01 07:30:00.0',14185,0,0,96,55),
(9,50,'2019-08-01 07:30:00.0',14184,0,0,58,48),
(9,50,'2019-08-01 07:30:00.0',14183,0,0,117,94),
(9,50,'2019-08-01 07:30:00.0',14182,0,0,3,2);
SELECT * FROM temp_sb;


ticker
contract
dt
price
p_open
p_close
volume
trades


9
50
2019-08-01 07:27:00
14186
0
0
106
13

9
50
2019-08-01 07:27:00
14185
0
0
112
87

9
50
2019-08-01 07:27:00
14184
0
0
210
77

9
50
2019-08-01 07:27:00
14183
0
0
89
55

9
50
2019-08-01 07:27:00
14182
0
0
343
138

9
50
2019-08-01 07:27:00
14181
0
0
95
63

9
50
2019-08-01 07:28:00
14187
0
0
3
3

9
50
2019-08-01 07:28:00
14186
0
0
16
13

9
50
2019-08-01 07:28:00
14185
0
0
71
60

9
50
2019-08-01 07:28:00
14184
0
0
93
79

9
50
2019-08-01 07:28:00
14183
0
0
37
31

9
50
2019-08-01 07:28:00
14182
0
0
52
43

9
50
2019-08-01 07:28:00
14181
0
0
247
194

9
50
2019-08-01 07:28:00
14180
0
0
415
140

9
50
2019-08-01 07:28:00
14179
0
0
3
3

9
50
2019-08-01 07:29:00
14185
0
0
133
94

9
50
2019-08-01 07:29:00
14184
0
0
96
73

9
50
2019-08-01 07:29:00
14183
0
0
73
48

9
50
2019-08-01 07:30:00
14185
0
0
96
55

9
50
2019-08-01 07:30:00
14184
0
0
58
48

9
50
2019-08-01 07:30:00
14183
0
0
117
94

9
50
2019-08-01 07:30:00
14182
0
0
3
2


CREATE TABLE `temp_nt` (
  `ticker` tinyint(4) unsigned NOT NULL,
  `contract` tinyint(6) unsigned NOT NULL,
  `dt` datetime NOT NULL,
  `p_open` mediumint(9) unsigned NOT NULL,
  `p_high` mediumint(9) unsigned NOT NULL,
  `p_low` mediumint(9) unsigned NOT NULL,
  `p_close` mediumint(9) unsigned NOT NULL,
  `volume` smallint(6) unsigned NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO temp_nt VALUES
(9,50,'2019-08-01 07:27:00.0',14185,14186,14181,14181,955),
(9,50,'2019-08-01 07:28:00.0',14181,14187,14179,14184,937),
(9,50,'2019-08-01 07:29:00.0',14185,14185,14183,14184,302),
(9,50,'2019-08-01 07:30:00.0',14184,14185,14182,14185,274);
SELECT * FROM temp_nt;


ticker
contract
dt
p_open
p_high
p_low
p_close
volume


9
50
2019-08-01 07:27:00
14185
14186
14181
14181
955

9
50
2019-08-01 07:28:00
14181
14187
14179
14184
937

9
50
2019-08-01 07:29:00
14185
14185
14183
14184
302

9
50
2019-08-01 07:30:00
14184
14185
14182
14185
274


EXPLAIN
SELECT temp_sb.ticker,
       temp_sb.contract,
       temp_sb.dt,
       temp_sb.price,
       (temp_sb.price = temp_nt.p_open) p_open,
       (temp_sb.price = temp_nt.p_close) p_close,
       temp_sb.volume,
       temp_sb.trades
FROM temp_sb
JOIN temp_nt USING(ticker, contract, dt)


id
select_type
table
partitions
type
possible_keys
key
key_len
ref
rows
filtered
Extra


1
SIMPLE
temp_nt
null
ALL
null
null
null
null
4
100.00
null

1
SIMPLE
temp_sb
null
ALL
null
null
null
null
22
4.55
Using where; Using join buffer (Block Nested Loop)


EXPLAIN
UPDATE temp_sb
JOIN temp_nt USING(ticker, contract, dt)
SET temp_sb.p_open = (temp_sb.price = temp_nt.p_open),
    temp_sb.p_close = (temp_sb.price = temp_nt.p_close);


id
select_type
table
partitions
type
possible_keys
key
key_len
ref
rows
filtered
Extra


1
SIMPLE
temp_nt
null
ALL
null
null
null
null
4
100.00
null

1
UPDATE
temp_sb
null
ALL
null
null
null
null
22
4.55
Using where


CREATE INDEX idx_emp_sb ON temp_sb (ticker, contract, dt);
CREATE INDEX idx_emp_nt ON temp_nt (ticker, contract, dt, p_open, p_close);
EXPLAIN
SELECT temp_sb.ticker,
       temp_sb.contract,
       temp_sb.dt,
       temp_sb.price,
       (temp_sb.price = temp_nt.p_open) p_open,
       (temp_sb.price = temp_nt.p_close) p_close,
       temp_sb.volume,
       temp_sb.trades
FROM temp_sb
JOIN temp_nt USING(ticker, contract, dt)


id
select_type
table
partitions
type
possible_keys
key
key_len
ref
rows
filtered
Extra


1
SIMPLE
temp_nt
null
index
idx_emp_nt
idx_emp_nt
13
null
4
100.00
Using index

1
SIMPLE
temp_sb
null
ref
idx_emp_sb
idx_emp_sb
7
fiddle_SUOCYPECIUVMMRYDDLAF.temp_nt.ticker,fiddle_SUOCYPECIUVMMRYDDLAF.temp_nt.contract,fiddle_SUOCYPECIUVMMRYDDLAF.temp_nt.dt
5
100.00
null


EXPLAIN
UPDATE temp_sb
JOIN temp_nt USING(ticker, contract, dt)
SET temp_sb.p_open = (temp_sb.price = temp_nt.p_open),
    temp_sb.p_close = (temp_sb.price = temp_nt.p_close);


id
select_type
table
partitions
type
possible_keys
key
key_len
ref
rows
filtered
Extra


1
SIMPLE
temp_nt
null
index
idx_emp_nt
idx_emp_nt
13
null
4
100.00
Using index

1
UPDATE
temp_sb
null
ref
idx_emp_sb
idx_emp_sb
7
fiddle_SUOCYPECIUVMMRYDDLAF.temp_nt.ticker,fiddle_SUOCYPECIUVMMRYDDLAF.temp_nt.contract,fiddle_SUOCYPECIUVMMRYDDLAF.temp_nt.dt
5
100.00
null


UPDATE temp_sb
JOIN temp_nt USING(ticker, contract, dt)
SET temp_sb.p_open = (temp_sb.price = temp_nt.p_open),
    temp_sb.p_close = (temp_sb.price = temp_nt.p_close);
    
SELECT * FROM temp_sb;


ticker
contract
dt
price
p_open
p_close
volume
trades


9
50
2019-08-01 07:27:00
14186
0
0
106
13

9
50
2019-08-01 07:27:00
14185
1
0
112
87

9
50
2019-08-01 07:27:00
14184
0
0
210
77

9
50
2019-08-01 07:27:00
14183
0
0
89
55

9
50
2019-08-01 07:27:00
14182
0
0
343
138

9
50
2019-08-01 07:27:00
14181
0
1
95
63

9
50
2019-08-01 07:28:00
14187
0
0
3
3

9
50
2019-08-01 07:28:00
14186
0
0
16
13

9
50
2019-08-01 07:28:00
14185
0
0
71
60

9
50
2019-08-01 07:28:00
14184
0
1
93
79

9
50
2019-08-01 07:28:00
14183
0
0
37
31

9
50
2019-08-01 07:28:00
14182
0
0
52
43

9
50
2019-08-01 07:28:00
14181
1
0
247
194

9
50
2019-08-01 07:28:00
14180
0
0
415
140

9
50
2019-08-01 07:28:00
14179
0
0
3
3

9
50
2019-08-01 07:29:00
14185
1
0
133
94

9
50
2019-08-01 07:29:00
14184
0
1
96
73

9
50
2019-08-01 07:29:00
14183
0
0
73
48

9
50
2019-08-01 07:30:00
14185
0
1
96
55

9
50
2019-08-01 07:30:00
14184
1
0
58
48

9
50
2019-08-01 07:30:00
14183
0
0
117
94

9
50
2019-08-01 07:30:00
14182
0
0
3
2

ticker	contract	dt	price	volume	trades
9	50	2019-08-01 07:27:00	14186	106	13
9	50	2019-08-01 07:27:00	14185	112	87
9	50	2019-08-01 07:27:00	14184	210	77
9	50	2019-08-01 07:27:00	14183	89	55
9	50	2019-08-01 07:27:00	14182	343	138
9	50	2019-08-01 07:27:00	14181	95	63
9	50	2019-08-01 07:28:00	14187	3	3
9	50	2019-08-01 07:28:00	14186	16	13
9	50	2019-08-01 07:28:00	14185	71	60
9	50	2019-08-01 07:28:00	14184	93	79
9	50	2019-08-01 07:28:00	14183	37	31
9	50	2019-08-01 07:28:00	14182	52	43
9	50	2019-08-01 07:28:00	14181	247	194
9	50	2019-08-01 07:28:00	14180	415	140
9	50	2019-08-01 07:28:00	14179	3	3
9	50	2019-08-01 07:29:00	14185	133	94
9	50	2019-08-01 07:29:00	14184	96	73
9	50	2019-08-01 07:29:00	14183	73	48
9	50	2019-08-01 07:30:00	14185	96	55
9	50	2019-08-01 07:30:00	14184	58	48
9	50	2019-08-01 07:30:00	14183	117	94
9	50	2019-08-01 07:30:00	14182	3	2

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	temp_nt	null	ALL	null	null	null	null	4	100.00	null
1	SIMPLE	temp_sb	null	ALL	null	null	null	null	22	4.55	Using where; Using join buffer (Block Nested Loop)