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:

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:

Advertisement

Answer

I am thinking of an updateable CTE and window functions:


Update: if the columns have the same name, then alias one of them:

User contributions licensed under: CC BY-SA
2 People found this is helpful
Advertisement