Complex update using joins and group by

Question

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_id = h.host_id WHERE g.guest_id = ... AND h.host_id = ... the pairs in the previous statement

Can you give some details on what trouble you're having? error messages? — kelloti
– kelloti, Commented Jan 20, 2011 at 5:52
@kelloti: No error, I just don't know how to convert this SELECT statement into an UPDATE statement — Moak
– Moak, Commented Jan 20, 2011 at 6:16

kelloti · Accepted Answer · 2011-01-20 07:00:10Z

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...

GaTechThomas · Accepted Answer · 2011-01-20 06:26:19Z

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.

greg · Accepted Answer · 2012-09-16 17:36:23Z

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.

Collectives™ on Stack Overflow

Complex update using joins and group by

3 Answers 3

Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Related