Question 351846 in Mysql Question posted in
The official MySQL documentation can be found here.

mysql table update with last value

i have two table

table1:

 id  |        time              |  value
----------------------------------------
 1   |  2024-01-09 22:24:14     |   100
----------------------------------------
 2   |  2024-01-10 22:25:14     |   200
----------------------------------------
 3   |  2024-01-11 22:26:14     |   300
----------------------------------------

table2:

id    |   last_value
----------------------
 1    |   ---
----------------------

i need to update table2.lastvalue with table1 last recede (2024-01-11 22:26:14 recode value 300)

how can i do that?

i try below script

update Table1, Table2 set Table2.last_value = Table1.Value where Table2.Id=1;

But every time update in first recede (100)

Tags:

2

Answers


  1. 0

    Use Max() function to get last DATE…
    With the data povided it should be 100 as the value for ID = 1 Is 100.

    --  S a m p l e   D a t a :
Create Table tbl_1 (ID Int, DT_TIME DATE, VAL Int);
Insert Into tbl_1 VALUES
(1, '2024-01-09 22:24:14', 100),
(2, '2024-01-10 22:25:14', 200),
(3, '2024-01-11 22:26:14', 300);

Create Table tbl_2 (ID Int, LAST_VAL Int);
Insert Into tbl_2 VALUES
(1, Null);

    … If you set a condition to any of the ID-s you’ll get that ID’s VAL.

    Update tbl_2 t2
Set t2.LAST_VAL = ( Select Max(VAL) From tbl_1 Where ID = t2.ID)
Where t2.ID = 1;
--  Rows matched: 1  Changed: 1  Warnings: 0
Select * From tbl_2;
/*
ID  LAST_VAL
--  --------
1        100    */

    … if there where multiple ID = 1 rows in sample tbl_1 …

    --  S a m p l e   D a t a :
Create Table tbl_1 (ID Int, DT_TIME DATE, VAL Int);
Insert Into tbl_1 VALUES
(1, '2024-01-09 22:24:14', 100),
(1, '2024-01-10 22:25:14', 200),
(1, '2024-01-11 22:26:14', 300);

    … then this same Update statement would result as:

    /*
ID  LAST_VAL
--  --------
1        300    */

    Finaly if you want to get last value of all rows and update tbl_2 with that value then just remove where close from subquery and filter the tbl_2 row(s) to be updated with that value:

    --  S a m p l e   D a t a :
Create Table tbl_1 (ID Int, DT_TIME DATE, VAL Int);
Insert Into tbl_1 VALUES
(1, '2024-01-09 22:24:14', 100),
(2, '2024-01-10 22:25:14', 200),
(3, '2024-01-11 22:26:14', 300);

    Update tbl_2 t2
Set t2.LAST_VAL = ( Select Max(VAL) From tbl_1 )
Where t2.ID = 1;

    /*
ID  LAST_VAL
--  --------
1        300    */
    Login or Signup to reply.
  2. 0

    To get the most recent table1.value row you can ORDER BY `time` DESC LIMIT 1:

    UPDATE table2
SET `last_value` = (SELECT value FROM table1 ORDER BY `time` DESC LIMIT 1)
WHERE id = 1;

    Here’s a db<>fiddle.

    Storing this redundant data is unlikely to provide any benefit over having an index on (`time`, `value`). There is almost certainly a better method, depending on your specific use case.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search