[Update query with join] Update db table based on joins #sql #database

update-query-with-join.sql

-- update a table using a standard query
with t as (
select <the primary key of the table> as rowid, <value to use to update> as update_value
from <table>
left join ....
where ....
)
update <table to update>
set <field to update> = t.update_value
from t
where <pk in table> = t.rowid

-- e.g:
with t as (
select fc.id as rowid, false as uncheckable
from consent_reading.field_check as fc
left join consent_reading.spreadsheet ss 
	on fc.spreadsheet_id = ss.id
where fc.data_correct is null and ss.data_ingested = true
)
update consent_reading.field_check
set uncheckable = t.update_value
from t
where field_check.id = t.rowid