SQL insert into another table migration with group by having max()

user_13808699 0
Viewed 117

I got the following problem. I got two tables and want to move one column data to another table.

The tables are house and door. I want to move the column data 'street' to house. But there are multiple doors for the house (with same house_id). So I need to aggregate them and want to take the 'street' input from the newest 'created_at' record.

situation

My first attempt is:

insert into house h ('street') 
    select street 
    from door d 
    where d.house_id = h.id 
    group by house_id 
    having max(created_at)
mariadb max group-by migration sql
1 Answers

I suspect that you actually want an update query, to set the value of street on existing rows in house.

One option is a correlated subquery with a row-limitig clause:

update house 
set street = (
    select d.street
    from door d
    where d.house_id = house.id
    order by created_at desc
    limit 1
)

The limit syntax varies across databases - but (almost) all have an equivalent functionality.

user_106767160
Related