UPDATE that uses DISTINCT

[Shelby500]

I have been working on an UPDATE query that will update one column based on results from a join select query.
[highlight #FCE94F]I am looking to update the column "stat_typ_cd='OPEN'" to "stat_typ_cd='CLOSED'".[/highlight]

At the bottom of the page is the SELECT query that pulls the data that I'm trying to update.

The UPDATE query below updates ALL rows, not just unique "DISTINCT" rows (which is what I want). This works though.​

update
(select su.sec_usr_id,su.is_enbl_ind,t.asn_usr_id,t.stat_typ_cd
  from sec_usr su, tsk t
  where su.is_enbl_ind = 0
  and su.sec_usr_id = t.asn_usr_id
  and t.stat_typ_cd = 'OPEN')u
set u.stat_typ_cd = 'CLOSED';

[highlight #FCE94F]Results:
*Action:
savepoint a
158 rows updated.[/highlight]

But, if I add “distinct” into it I get an illegal on this view error. Fails.​

update
(select [COLOR=#EF2929]distinct su.sec_usr_id[/color], su.is_enbl_ind, t.asn_usr_id, t.stat_typ_cd
  from sec_usr su, tsk t
  where su.is_enbl_ind = 0
  and su.sec_usr_id = t.asn_usr_id
  and t.stat_typ_cd = 'OPEN')u
set u.stat_typ_cd = 'CLOSED';

[highlight #FCE94F]Results:
SQL Error: ORA-01732: data manipulation operation not legal on this view
01732. 00000 -  "data manipulation operation not legal on this view"[/highlight]

SELECT query below pulls the data that the UPDATE needs to update.​

SELECT [COLOR=#EF2929]distinct n.sec_usr_id[/color], n.is_enbl_ind, m.stat_typ_cd, m.asn_usr_id
FROM sec_usr n
LEFT OUTER JOIN tsk m
ON n.sec_usr_id = m.asn_usr_id
where n.is_enbl_ind = 0
and m.stat_typ_cd = 'OPEN';

 

[SkipVought]

Hi

Distinct to what?

Distinct is used to display one row for each column in a Select when there are multiple occurrences.

 

[LKBrwnDBA]

SELECT query below pulls the data that the UPDATE needs to update.

No it doesn't, because you are omitting the duplicates -- or -- is it you only want to update ONE of the duplicate rows?


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[Shelby500]

You guys are correct. Too much time staring at a computer screen I guess. lol

 

[Shelby500]

Sorry, one more question. The DBA gave me this below but to me it looks like it finds the unique sec_usr_id's and then updates the stat_typ_cd. Isn't this way pretty inefficient to use? This is my last question and then I'm going to get a cup of coffee and bang my head against the wall. His title is DBA not his expertise so I don't trust this code.

merge into tsk
using (
select distinct su.sec_usr_id,t.stat_typ_cd
from tsk t, sec_usr su
where su.is_enbl_ind = 0
and su.sec_usr_id = t.asn_usr_id
and t.stat_typ_cd = 'OPEN'
) f
on ( f.sec_usr_id = tsk.asn_usr_id )
when matched
then update
set tsk.stat_typ_cd = 'CLOSED';