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:
x
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