CODEWITHSUNDEEP
mysql
Naguib Ihab
Naguib Ihab

Reputation: 4496

Sql update a table based on the values of a join from another table

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

Upvotes: 0

Views: 38

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269793

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;

Upvotes: 1

Related Questions