best way to replace a value in 2nd table where value in 1st table has updated?

Question

im trying to find the last time a value was inputted into table 1 and then input that value into table 2 however due to the sheer size of table 1 (this gets truncated daily) it either takes forever or i end up losing connection to the server

I tried to use a after insert trigger but it would run i then created an event to run every couple hours but again after the 1st time it would run.

The query im currently using is

replace into database.table2 (value1,value2,date,time)
select value1,value2,date,time from database.table1;

value 1 is a unique value which is the reason for using replace as I only want 1 instance (string) of this value.

Example Data Table1

----------------------------------------------------
| Value1 | Value2       | DATE          | TIME     |
--------------------------------------------------
| 1      | red          | 01/08/2018    | 10:33:15 |
| 2      | red          | 01/08/2018    | 10:33:20 |
| 3      | red          | 01/08/2018    | 10:33:21 |
| 4      | red          | 01/08/2018    | 10:33:23 |
| 1      | red          | 01/08/2018    | 10:36:15 |
| 1      | red          | 01/08/2018    | 10:38:15 |
| 2      | red          | 01/08/2018    | 10:38:15 |
----------------------------------------------------

Example Data Table2 Desired Outcome where the value pulled over from table1 is the latest value in the table

----------------------------------------------------
| Value1 | Value2       | DATE          | TIME     |
--------------------------------------------------
| 1      | red          | 01/08/2018    | 10:38:15 |
| 2      | red          | 01/08/2018    | 10:38:15 |
| 3      | red          | 01/08/2018    | 10:33:21 |
| 4      | red          | 01/08/2018    | 10:33:23 |
----------------------------------------------------


CREATE TABLE table1 (

value1 int primary key,
value2 varchar(10),
Date date,
Time time
);

INSERT INTO table1 (value1,value2, Date, Time)
Values (1, 'red', '2018-08-01', '10:33:15' ),
(2, 'red', '2018-08-01', '10:33:20' );
(3, 'red', '2018-08-01', '10:33:21' );
(4, 'red', '2018-08-01', '10:33:23' );
(1, 'red', '2018-08-01', '10:36:15' );
(1, 'red', '2018-08-01', '10:18:15' );
(2, 'red', '2018-08-01', '10:38:15' );



CREATE TABLE table2 (

value1 varchar(10) primary key,
value2 varchar(10),
Date date,
Time time
);

Hopefully i have written those correctly as i normally use workbench to create tables

Can you show the `SHOW CREATE TABLE table` statement for every table involved in the question? — Raymond Nijland, Aug 01 '18 at 14:58
Besides your current query is pretty wrong you can't trust MySQL into selecting the records in the same order always without using ORDER BY. — Raymond Nijland, Aug 01 '18 at 15:00
will replace into not take the latest value in table 1? my issue was it was just taking up a lot of resources? — Avalon Parkes-barton, Aug 01 '18 at 15:04
Talking about the `select value1,value2,date,time from database.table1;` — Raymond Nijland, Aug 01 '18 at 15:05
The query also would be more eazier when you use datetime datatype instead of using two columns — Raymond Nijland, Aug 01 '18 at 15:07
the date and time are kept seperate due to reporting in something else which needs those 2 columns seperate — Avalon Parkes-barton, Aug 01 '18 at 15:14
@AvalonParkes-barton That sounds like an implausible excuse, but if true, have a separate column anyway that stores the date and time correctly, as a single entity. (Well done for almost providing DDLs, BTW) — Strawberry, Aug 01 '18 at 15:27
you have lost me now haha im not sure what ddl's are ? i mean the only reason for the seperate columns are due to sorting of datatables on an external webpage I guess i can join these together and still get the same outcome — Avalon Parkes-barton, Aug 01 '18 at 15:33

Strawberry · Answer 1 · 2018-08-01T15:44:11.063

0

Not an answer; too long for a comment.

Let's start with the only bit of this that presently makes any sense:

INSERT INTO Test (id,present,datetime) VALUES
(1, 'red', '2018-08-01 10:33:15' ),
(2, 'red', '2018-08-01 10:33:20' );
(3, 'red', '2018-08-01 10:33:21' );
(4, 'red', '2018-08-01 10:33:23' );
(1, 'red', '2018-08-01 10:36:15' );
(1, 'red', '2018-08-01 10:18:15' );
(2, 'red', '2018-08-01 10:38:15' );

This data set has no PRIMARY KEY. Before we go any further, it needs one.

edited Aug 01 '18 at 15:44

answered Aug 01 '18 at 15:35

Strawberry

33,750
13
40
57

is value1 not the primary key? sorry i added the wrong header on create table i have updated them – Avalon Parkes-barton Aug 01 '18 at 15:41
Let's stick with the headings above. The first requirement of a primary key is that it's unique. Clearly, id is not unique. – Strawberry Aug 01 '18 at 15:43
ah okay i get you now urm no then value1 is only unique in table 2 – Avalon Parkes-barton Aug 01 '18 at 15:49

score 0 · Answer 2 · answered Aug 01 '18 at 15:44

To find most recent values you can do something like this

SELECT t1.value1, t1.value2, t1.date, t1.time
FROM table1 t1 LEFT JOIN table1 t2
  ON (t1.value1 = t2.value1 AND concat(t1.date,' ',t1.time) < concat(t2.date,' ',t2.time)) WHERE t2.date IS NULL;

AND then use that with your Replace

REPLACE INTO table2 (value1,value2,date,time)
SELECT t1.value1, t1.value2, t1.date, t1.time
    FROM table1 t1 LEFT JOIN table1 t2
      ON (t1.value1 = t2.value1 AND concat(t1.date,' ',t1.time) < concat(t2.date,' ',t2.time)) WHERE t2.date IS NULL;

Fiddle: http://sqlfiddle.com/#!9/dbbc27/1 Reference: https://stackoverflow.com/a/1313293/2397717

so this works but im still getting the issue that its taking so long using replace that its losing connection from the server 4/5 attempts i mean in t1 i have over 400000 entry value's — Avalon Parkes-barton, Aug 01 '18 at 15:52

best way to replace a value in 2nd table where value in 1st table has updated?

2 Answers2