MySQL update one table from another joined many-to-many

join;MySQLupdate

I have two tables, events and plays joined many-to-many by a third table plays_in_events.

Where the events.Name field is blank, I want to set it equal to the name of the play that was performed in that linked event.

I have tried

UPDATE `events` 
  INNER JOIN `events` ON (`plays_in_events`.`EventID` = `events`.`EventID`)
  INNER JOIN `plays` ON (`plays_in_events`.`PlayID` = `plays`.`PlayID`)
  SET events.Name = plays.Play
WHERE 
  events.Name IS NULL and plays_in_events.PlayID = 1

but get a syntax error

SQL Error: Not unique table/alias: 'events'

(I'm trying to limit any damage from accidental multiple updates by only testing it in the first instance for PlayID = 1, but have hundreds of records to update, so I don't want to do it manually).

There is only one table events in the database.

What's going wrong here please? I can't see it, unfortunately.

LATER

Maybe I've got the JOIN wrong altogether.

This SELECT query works to join the two main tables, and was produced by a visual query editor:

SELECT 
  `events`.`EventID`, `events`.`Name`, `plays`.`Play`, `plays`.`PlayID`
FROM
  `plays_in_events`
  INNER JOIN `events` ON (`plays_in_events`.`EventID` = `events`.`EventID`)
  INNER JOIN `plays` ON (`plays_in_events`.`PlayID` = `plays`.`PlayID`)
WHERE
  `events`.`Name` IS NULL AND 
  `plays`.`PlayID` < 10

How could I make the JOINS in the UPDATE based on this working?

Best Answer

This works - I had the JOIN wrong

UPDATE `events` 
  INNER JOIN `plays_in_events` ON (`plays_in_events`.`EventID` = `events`.`EventID`)
  INNER JOIN `plays` ON (`plays_in_events`.`PlayID` = `plays`.`PlayID`)
  SET events.Name = plays.Play
WHERE 
  events.Name IS NULL and plays_in_events.PlayID = 1

and the earlier comments were quite right, but it took me a while to 'get it' - thanks for your help. I had just copied the JOIN lines from the working SELECT query, but when I looked again, I could finally see what the commentators meant about events appearing twice, and plays_in_events not appearing properly in the JOINs.

Related Solutions

Mysql – Optimizing multi-table left joins

PLEASE use SHOW CREATE TABLE; DESCRIBE is not descriptive enough.

You seem to be working with IPv4 IP addresses. IPv6 is upon us!

Your query inherently must do a full table scan in order to get the "total" upon which you do the ORDER BY. Since there are a million rows, it will take time.

count('*')

The quotes are not needed.

The two SELECTs are likely to be identical; order across JOIN does not matter. It does matter across LEFT JOIN. (INNER and OUTER add nothing.)

Suggestion...

First, see if this gives the correct information (albeit incomplet info):

SELECT  e.sid, e.cid, COUNT(*) AS total
FROM  event AS e
JOIN  iphdr as h ON e.sid = h.sid
                AND  e.cid = h.cid
WHERE  e.is_deleted = 0
GROUP BY  i.ip_src, i.ip_dst
ORDER BY  total DESC
LIMIT  5;

If so, then use that as a subquery, a la FROM (that-select) and LEFT JOIN to the other two tables to get the rest of the info.

The trick... The subquery is doing less work since it is touching 2 tables (vs 4). The LEFT JOINs only have to do 5 probes each, not a million.

Oracle: Non key-preserved table should be

You can update a join in Oracle if the following conditions are met:

Only one base table is updated
All other tables are key-preserved: each of them must have at most one row for each row of the base table.

(additional restrictions on updating views apply)

In your example you update table PG_MACHINE only. Oracle has to make sure that for a single row of this table, only one row of the other can be found. This seems to be the case since you have a PK on PG_LABLOCATION.LABLOCID. Therefore you should be able to update the join. See for example this SQLFiddle with a similar setup.

In your case you should either:

make sure that the primary key is enabled, validated, not deferrable (interestingly, a deferrable constraint prevents Oracle from updating the join!)
use MERGE if PG_LABLOCATION.LABLOCID is unique for the relevant query. MERGE is less strict than update with joins and will only return an error if there is actually a duplicate in the result set (whereas UPDATE will fail if there is the possibility of a duplicate).

review your query, since you don't need values from the parent table in the SELECT clause, you could rewrite it as a semi-join (that guarantees that no duplicate will be generated):

UPDATE (SELECT mac.in_use, mac.update_time
          FROM pg_machine mac
         WHERE mac.lablocid IN (SELECT loc.lablocid 
                                  FROM pg_lablocation loc 
                                 WHERE loc.dnsname = 'value')
           AND to_date('02-JAN-2013') > mac.update_time)
   SET in_use = 1 - MOD(101, 2), 
       update_time = to_date('02-JAN-2013');

This could be rewritten as:

UPDATE pg_machine mac
   SET in_use = 1 - MOD(101, 2), 
       update_time = to_date('02-JAN-2013')
 WHERE mac.lablocid IN (SELECT loc.lablocid 
                          FROM pg_lablocation loc 
                         WHERE loc.dnsname = 'value')
   AND to_date('02-JAN-2013') > mac.update_time;

In this case I would go with the third option: in general you can't update the parent in a parent-child join.

Best Answer

Related Solutions

Mysql – Optimizing multi-table left joins

Oracle: Non key-preserved table should be

Related Question