MSB MSB - 3 months ago 8
MySQL Question

MYSQL Update single records a table with MIN & MAX values from multiple records in 2nd table MYSQL

I have 2 tables houses & availability - I want to find the min and max for the RENT field for each HOUSEID in AVAILABILITY this contains many records per JOUSEID , then UPDATE the HOUSES table fields MIN_RENT and MAX_RENT, the HOUSES table contains only one entry per HOUSE ID

HOUSES

houseid min_rent max_rent
121 40.00 90.00
122 50.00 80.00
123 40.00 100.00

AVAILABILITY

house_id date rent
121 01/01/12 40
121 01/02/12 50
121 01/03/12 60
121 01/04/12 90
122 01/01/12 40
122 01/02/12 50
122 01/03/12 60
122 01/04/12 80
123 01/01/12 40
123 01/02/12 50
123 01/03/12 60
123 01/04/12 90


Regards
Martyn

Answer

a faster solution

UPDATE
     HOUSES H
    ,(
        SELECT
             house_id
            ,MIN( rent ) AS min 
            ,MAX( rent ) AS max
        FROM
            AVAILABILITY
        GROUP BY
            house_id
    ) AS A
SET
     min_rent = A.min
    ,max_rent = A.max
WHERE
    H.house_id = A.house_id
Comments