updating a table by joining multiple tables

Question

I have the following query

SELECT e.topicShortName, d.catalogFileID, e.topicID
FROM catalog_topics a
LEFT JOIN catalog_files_join b ON a.catalogID = b.foreignKey
LEFT JOIN catalog_files_join c ON c.foreignKey = b.catalogFileID
LEFT JOIN catalog_files d ON d.catalogFileID = b.catalogFileID
LEFT JOIN catalog_lu_topics e ON a.topicID = e.topicID
WHERE b.fileTypeID = 'gvl401'
AND c.fileTypeID = 'gvl25'
AND e.parentID = 'top305'
AND a.sortorder =1
AND e.topicID = 'top318'

which fetches me one row of data:

topicShortName  catalogFileID  topicID
 Welcoming       cfil960       top318

I want to run a update statement so that I can update catalogFileID to 'cfil123'. I have the topicID with me, it is 'top318'

catalogFileID belongs to catalog_files

I cant seem to wrap my head around the update statement which will achieve this..

I do not mind doing multiple updates. But after the update statements, the above select query should return cfil123. But I cant just update all the tables where catalogFileID is used..

CORRECT ANSWER:

UPDATE catalog_topics a
LEFT JOIN catalog_files_join b ON a.catalogID = b.foreignKey
LEFT JOIN catalog_files_join c ON c.foreignKey = b.catalogFileID
LEFT JOIN catalog_files d ON d.catalogFileID = b.catalogFileID
LEFT JOIN catalog_lu_topics e ON a.topicID = e.topicID
SET d.catalogFileID = 'Cfil123',
    b.catalogFileID = 'Cfil123',
    c.foreignKey = 'Cfil123'
WHERE b.fileTypeID = 'gvl401'
AND c.fileTypeID = 'gvl25'
AND e.parentID = 'top305'
AND a.sortorder =1
AND e.topicID = 'top318'

Answer 1

Better to begin a transaction and update each and every table separately. SQL update statements are meant to affect one table per statement.

Answer 2

1. Identify all the tables that have this ID as a foreign key

2. Wrap your code in a transaction

3. List out individual updates. I can't tell exactly what your schema is, but I think it's:

   UPDATE category_files set catalogFileID='cfil123' where categoryFileID='cfil960'; UPDATE catalog_files_join set catalogFileID='cfil123' where categoryFileID='cfil960' etc.

This would update all references to the category file, which may not be what you want.

Answer 3

Simply replace 'SELECT...FROM' with 'UPDATE' and add a 'SET ...' clause before WHERE:

UPDATE catalog_topics a
LEFT JOIN catalog_files_join b ON a.catalogID = b.foreignKey
LEFT JOIN catalog_files_join c ON c.foreignKey = b.catalogFileID
LEFT JOIN catalog_files d ON d.catalogFileID = b.catalogFileID
LEFT JOIN catalog_lu_topics e ON a.topicID = e.topicID
SET d.catalogFileID = 'cfil123',
    b.catalogFileID = 'cfil123'
WHERE b.fileTypeID = 'gvl401'
AND c.fileTypeID = 'gvl25'
AND e.parentID = 'top305'
AND a.sortorder =1
AND e.topicID = 'top318'

Make sure you specify which tables' fields to update in the SET clause using <table>.<field> notation.

Edit: Removed extra comma ...