Complex update using joins and group by

This query selects ID's from two tables where there are exactly one to one name matches

SELECT d.guest_id, h.host_id
FROM `guest` AS g 
JOIN `guest_data` AS d 
    ON d.guest_id = g.guest_id 
JOIN host_data AS h 
    ON d.guest_nm = h.host_nm 
GROUP BY
    h.venue_nm 
HAVING COUNT(*) = 1

Now I'm having trouble updating guest table (g) to set the g.link_id = h.host_id

Basically I want to create a statement like

UPDATE `guest` AS g , `host` h
SET g.link_i开发者_运维技巧d = h.host_id
WHERE g.guest_id = ... AND h.host_id = ... the pairs in the previous statement

A problem you might be having is that you could have many host_data rows for each guest, so you have to use an aggregate function (I used max below) to get to the single row you want to pull a host_id from. I'm more of a SQL Server person, but I think this syntax is pretty close to what you'll use on MySQL. Here's my select:

SELECT g.link_id, 
    (
        SELECT MAX(h.host_id)
        FROM guest_data d
        INNER JOIN host_data h ON d.guest_nm=hhost_nm
        GROUP BY h.venue_nm
        HAVING COUNT(*) = 1
    ) AS x
FROM guest g
WHERE g.guest_id IN 
(
    SELECT d.guest_id
    FROM guest_data d
    INNER JOIN host_data h ON d.guest_nm=hhost_nm
    GROUP BY h.venue_nm
    HAVING COUNT(*) = 1
)

After checking that the select returns the right result set you can easily convert that into an UPDATE statement:

UPDATE guest g
SET link_id=
    (
        SELECT MAX(h.host_id)
        FROM guest_data d
        INNER JOIN host_data h ON d.guest_nm=hhost_nm
        WHERE d.guest_id=g.guest_id
        GROUP BY h.venue_nm
        HAVING COUNT(*) = 1
    )
WHERE g.guest_id IN 
(
    SELECT d.guest_id
    FROM guest_data d
    INNER JOIN host_data h ON d.guest_nm=hhost_nm
    GROUP BY h.venue_nm
    HAVING COUNT(*) = 1
)

I hope this is close enough to be of help...

In Sql Server, you would do something like

UPDATE guest SET link_id = host_id FROM guest g ...

I've never used mysql, so fingers crossed.

After looking at the syntax for this a bit, I decided to break up the operations and used the same SELECT query to INSERT INTO a temporary table. Then I performed the UPDATE using typical JOIN syntax.

This has the added benefit of being able to verify the query results before pushing to your table.