I have 3 tables with following columns.
- Table: A with column: newColumnTyp1, typ2
- Table: B with column: typ2, tableC_id_fk
- Table: C with column: id, typ1
I wanted to update values in A.newColumnTyp1 from C.typ1 by following logic:
- if A.typ2=B.typ2 and B.tableC_id_fk=C.id
- the values must be distinct, if any of the conditions above gives multiple results then should be ignored. For example A.typ2=B.typ2 may give multiple result in that case it should be ignored.
edit:
- the values must be distinct, if any of the conditions above gives multiple results then take only one value and ignore rest. For example A.typ2=B.typ2 may give multiple result in that case just take any one value and ignore rest because all the results from A.typ2=B.typ2 will have same B.tableC_id_fk.
I have tried:
SELECT DISTINCT C.typ1, B.typ2 FROM C LEFT JOIN B ON C.id = B.tableC_id_fk LEFT JOIN A ON B.typ2= A.typ2
it gives me a result of table with two columns typ1,typ2 My logic was, I will then filter this new table and compare the type2 value with A.typ2 and update A.newColumnTyp1 I thought of something like this but was a failure:
update A set newColumnTyp1= ( SELECT C.typ1 from SELECT DISTINCT C.typ1, B.typ2 FROM C LEFT JOIN B ON C.id = B.tableC_id_fk LEFT JOIN A ON B.typ2= A.type2 where A.typ2=B.typ2);
Advertisement
Answer
I am thinking of an updateable CTE and window functions:
with cte as ( select a.newColumnTyp1, c.typ1, count(*) over(partition by a.typ2) cnt from a inner join b on b.type2 = a.typ2 inner join c on c.id = b.tableC_id_fk ) update cte set newColumnTyp1 = typ1 where cnt > 1
Update: if the columns have the same name, then alias one of them:
with cte as ( select a.typ1, c.typ1 typ1c, count(*) over(partition by a.typ2) cnt from a inner join b on b.type2 = a.typ2 inner join c on c.id = b.tableC_id_fk ) update cte set typ1 = typ1c where cnt > 1