0

Based on this post this should work, but it isn't working correctly:

UPDATE `cms_download` 
SET folder_id = '230'
FROM `cms_download` file
LEFT JOIN `cms_download_folder` folder
ON file.folder_id = folder.folder_id
WHERE file.client_id = '69'
AND folder.folder_id = NULL

What I have is a files table (cms_download) which contains a column that refers to the folder that contains this file (cms_download_folder). And what I'm trying to do is set all the files that do not have a folder assigned to them to a particular folder. I can know that by left or right joining in the two tables together on the folder id, and where the entry doesn't exist in the (cms_download_folder) table against the entry in (cms_download) table I would change the folder_id in the (cms_download) table so that it would refer to an entry that exists.

I hope I'm making sense.

Thanks to Gordon, I got to the correct query which is:

UPDATE  `cms_download` file 
LEFT JOIN  `cms_download_folder` folder 
ON file.folder_id = folder.folder_id 
SET file.folder_id =  '230' 
WHERE file.client_id =  '69' 
AND folder.folder_id = NULL
Community
  • 1
  • 1
Naguib Ihab
  • 4,259
  • 7
  • 44
  • 80

1 Answers1

1

The issue is that you are using SQL Server syntax but your post is tagged MySQL. The join goes before the set in MySQL:

UPDATE `cms_download` file LEFT JOIN
       `cms_download_folder` folder
        ON file.folder_id = folder.folder_id
    SET folder_id = '230'
WHERE file.client_id = '69' AND folder.folder_id = NULL;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786