Perform UPDATE on rows from a SELECT
I've searched for hours but I can't get this to work. I have a SELECT that works but can't get the syntax right to feed it to UPDATE.
The following SELECT does work and gives me the rows that I want to UPDATE:
SELECT ALL path.idPath, path.strPath, files.strFileName, files.playCount, files.lastPlayed
FROM path INNER JOIN files ON path.idPath = files.idPath
WHERE files.playCount IS NULL AND path.strPath LIKE 'smb%_ARCHIVE_%' AND files.strFileName <> ""
For these rows, I want to:
SET files.playCount = 1, files.lastPlayed = '2017-01-29 12:00:00'
but I can't figure out how to combine them. This DOES NOT work:
UPDATE files, path INNER JOIN files ON path.idPath = files.idPath
SET files.playCount = 1, files.lastPlayed = '2017-01-29 12:00:00'
SELECT ALL path.idPath, path.strPath, files.strFileName, files.playCount, files.lastPlayed
WHERE files.playCount IS NULL AND path.strPath LIKE 'smb%_ARCHIVE_%' AND files.strFileName <> ""
I'm guessing it's an easy answer without understanding the tables, but a bit of detail just in case:
I want to update playCount and lastPlayed in the files table if playCount IS NULL. This table holds a foreign key (idPath) to the path table, where I make a selection based on the strings in a couple of fields there.
The SELECT above returns the correct rows. I just can't figure out how to feed these rows to the UPDATE.
I am selecting more fields than I need to update, but that's just so I can tell the SELECT is working properly when run alone. I hope this isn't a problem for the combined UPDATE?