INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

UPDATE that uses DISTINCT

UPDATE that uses DISTINCT

(OP)
I have been working on an UPDATE query that will update one column based on results from a join select query.
I am looking to update the column "stat_typ_cd='OPEN'" to "stat_typ_cd='CLOSED'".

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.

CODE

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'; 

CODE -->

Results:
*Action:
savepoint a
158 rows updated. 

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

CODE

update
(select distinct 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'; 

CODE -->

Results:
SQL Error: ORA-01732: data manipulation operation not legal on this view
01732. 00000 -  "data manipulation operation not legal on this view" 

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

CODE

SELECT distinct n.sec_usr_id, 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'; 

RE: UPDATE that uses DISTINCT

Hi

Distinct to what?

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

RE: UPDATE that uses DISTINCT

Quote (Shelby500)

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?
banghead

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

RE: UPDATE that uses DISTINCT

(OP)
cursehairpull You guys are correct. Too much time staring at a computer screen I guess. lol thanks2

RE: UPDATE that uses DISTINCT

(OP)
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.

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'; 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close