By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE booked (
ID_Booking INT,
House_ID INT,
House_Price FLOAT
);
INSERT INTO booked
VALUES
(3, 1, 40000),
(4, 1, 200000);
CREATE TABLE house_info (
House_ID INT,
House_Price FLOAT
);
INSERT INTO house_info
VALUES
(1, 40000),
(2, 1000000);
-- Before UPDATE
select * from house_info;
| House_ID | House_Price |
|---|---|
| 1 | 40000 |
| 2 | 1000000 |
select b1.House_Price, b2.House_ID
from booked b1
join ( select House_ID,max(ID_Booking) as ID_Booking
from booked
group by House_ID
) b2
on b1.House_ID = b2.House_ID and b1.ID_Booking = b2.ID_Booking;
| House_Price | House_ID |
|---|---|
| 200000 | 1 |
update house_info h join (
select b1.House_Price, b2.House_ID
from booked b1
join ( select House_ID,max(ID_Booking) as ID_Booking
from booked
group by House_ID
) b2
on b1.House_ID = b2.House_ID and b1.ID_Booking = b2.ID_Booking
) bb on bb.House_ID = h.House_ID
set h.House_Price = bb.House_Price;
-- After UPDATE
select * from house_info;
| House_ID | House_Price |
|---|---|
| 1 | 200000 |
| 2 | 1000000 |