MySQL Update Table 1 when LEFT JOIN table contains multiple matches per row-CodePudding

I have two tables that are joined on a command column. I need to update table 1 where an item in table 2 has multiple matches for the id.

Table 1 items:

 ------------------- ------------------ ------ ----- --------------------- ------------------- 
| Field             | Type             | Null | Key | Default             | Extra             |
 ------------------- ------------------ ------ ----- --------------------- ------------------- 
| id                | int unsigned     | NO   | PRI | NULL                | auto_increment    |
| name              | varchar(255)     | NO   | UNI | NULL                |                   |
| vendors_id        | tinyint unsigned | NO   | MUL | NULL                |                   |
| multiple_vendors  | tinyint unsigned | NO   | MUL | NULL                |                   |
 ------------------- ------------------ ------ ----- --------------------- -------------------

Table 2 item_details:

| Field             | Type             | Null | Key | Default           | Extra             |
 ------------------- ------------------ ------ ----- ------------------- ------------------- 
| itmes_id          | int unsigned     | NO   | PRI | NULL              |                   |
| vendors_id        | tinyint unsigned | NO   | PRI | NULL              |                   |
| location          | varchar(255)     | YES  | MUL | NULL              |                   |
| color             | varchar(255)     | YES  | MUL | NULL              |                   |
| description_short | varchar(255)     | YES  | MUL | NULL              |                   |
| description_long  | text             | YES  |     | NULL              |                   |
 ------------------- ------------------ ------ ----- ------------------- -------------------

The items table and the item_details table are joined by item_details.items_id = items.id.

There could be multiple entires for an individual item in item_details from different vendors. If I have an item from multiple vendors I want to update items.multiple_vendors = 1. I am trying to figure out how to run an update on items that checks if count(item_details.vendors_id > 1.

I was attempting with the following query, however I got the error ERROR 1111 (HY000): Invalid use of group function

update items i left join item_details id on id.items_id = i.id set i.multiple_vendors = 1 where count(distinct(id.vendors_id)) > 1 ;

Thanks in advance for the help~

CodePudding user response：

You can't use an aggregation function in WHERE, because aggregation doesn't happen until after you select rows.

Join with a subquery that only returns the rows with multiple matches.

UPDATE items AS i
JOIN (
    SELECT items_id
    FROM item_details
    GROUP BY items_id
    HAVING COUNT(DISTINCT vendors_id) > 1
) AS id ON id.items_id = i.id
SET i.multiple_vendors = 1