Skip to content
Advertisement

SQL Complex update query filter distinct values only

I have 3 tables with following columns.

  1. Table: A with column: newColumnTyp1, typ2
  2. Table: B with column: typ2, tableC_id_fk
  3. Table: C with column: id, typ1

I wanted to update values in A.newColumnTyp1 from C.typ1 by following logic:

  1. if A.typ2=B.typ2 and B.tableC_id_fk=C.id
  2. 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:

  1. 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
User contributions licensed under: CC BY-SA
2 People found this is helpful
Advertisement