CODEWITHSUNDEEP
postgresql
goku
goku

Reputation: 213

sql update table condition depends on other rows in the same table

I have the following table

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
...

I want to write a query to check download=1 and update all the rows with the same url and downoload=0 to set the same filename and download=1

so the output would be

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

Upvotes: 1

Views: 349

Answers (1)

S-Man
S-Man

Reputation: 23676

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. Find the expected record with download = 1
  2. Find all related url records
  3. Update the columns with the data fetched in step 1

Upvotes: 2

Related Questions