Updating values based on whether a value exists

Question

I can't seem to get my head around the situation where you have a table that has idents listed multiple times for different FAM_TYPES, as shown below for one ident example. Basically, I want to update the VALID_TO column to match the VALID_FROM column for all FAM_TYPEs but only where there is a FAM_TYPE of ACT and the associated FAM_VALUE is 1 for that IDENT. I have tried using WHERE statements but I end up only updating the row where the FAM_TYPE is ACT.

IDENT   FAM_TYPE    FAM_VALUE   VALID_FROM  VALID_TO
61593   ACT            1        13/11/2017  12/01/2018
61593   LSF            1        13/11/2017  12/01/2018
61593   ACT            1        13/11/2017  22/12/2017
61593   ACT            1        13/11/2017  22/12/2017

Answer 1

Existence can be tested with IN and a sub-SELECT in ADS.

You probably want something like this:

UPDATE
  table
SET
  valid_to = valid_from
WHERE
  ident IN (SELECT ident from table WHERE fam_type = 'ACT' AND fam_value = 1)