CODEWITHSUNDEEP
phpmysqlselect
MSB
MSB

Reputation: 37

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

Upvotes: 0

Views: 2409

Answers (1)

ncank
ncank

Reputation: 956

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

Upvotes: 2

Related Questions