SQL更新表的条件取决于同一表中的其他行

Question

我有以下表格

id,link_id,url,type,download,filename
44,11,https://google.com,extra,1,126cd08a-b963-48e5-878e-96dea057d57e.jpg
45,11,https://google.com,extra,0,53bfa01d-91d0-4b84-9389-b06e5e4ef618.jpg
46,11,https://google.com,extra,0,364cfdc2-c0b6-43fc-8936-33e49896014a.jpg
47,12,https://google.com,extra,0,9d26efbd-e6e0-42df-bde0-04c05babffe4.jpg
48,13,https://yahoo.com,extra,0,2d58b9f7-1860-40d8-88f0-9fc08cd7275f.jpg
49,13,https://yahoo.com,extra,0,574b1646-6316-4a4b-8e28-56c38c0999b9.jpg
...

我想写一个查询来检查下载=1和更新所有行与相同的url和下载=0设置相同的文件名和下载=1

那么输出将是

44,11,https://google.com,extra,1,126cd08a-b963-48e5-878e-96dea057d57e.jpg
45,11,https://google.com,extra,1,126cd08a-b963-48e5-878e-96dea057d57e.jpg
46,11,https://google.com,extra,1,126cd08a-b963-48e5-878e-96dea057d57e.jpg
47,12,https://google.com,extra,1,126cd08a-b963-48e5-878e-96dea057d57e.jpg
48,13,https://yahoo.com,extra,0,2d58b9f7-1860-40d8-88f0-9fc08cd7275f.jpg
49,13,https://yahoo,extra,0,574b1646-6316-4a4b-8e28-56c38c0999b9.jpg

Answer 1

demo:db<>fiddle

UPDATE mytable t
SET download = s.download, filename = s.filename   -- 3 
FROM (
SELECT url, download, filename                 -- 1
FROM mytable
WHERE download = 1
) s
WHERE t.url = s.url                                -- 2

1. 查找download = 1
期望记录
2. 查找所有相关url记录
3. 用步骤1中获取的数据更新列