Select statement with exists 3

[cas85]

My database has the following 2 tables:

RqProjectDocuments RqDocuments

Id DocumentId ID
12 10 10
12 11 11
12 12 12
12 13 13
13 20 20
13 21 21
13 22 22
13 23 23

This select statement works as expected and displays 4 rows.

select Name, ProjectId, DocumentId RqDocID, id
from RqProjectDocuments A, RqDocuments B where A.DocumentId = B.Id and projectId = 12

I then wanted to update Rqdocuments with the following SQL statements. But this statement updates all the records in Rqdocuments. It should only update 4? Any Ideas?


update RqDocuments
set RqDocuments.FileDirectory = 'c:\Projects',VersionNumber = VersionNumber + .0001,VersionUserId = 99,
VersionDateTime = Sysdate, VersionReason = 'RequistePro Document Update'
where exists
(select A.ProjectId, A.DocumentId, B.id
from RqProjectDocuments A, RqDocuments B where A.DocumentId = B.Id and A.ProjectId = 12)

 

[SantaMufasa]

CAS,

Your UPDATE statement did what you said to do, not what you wanted it to do. Here is the rationale of your UPDATE statement's behavior:

First, we already know that when you execute the "(SELECT...)" clause, it returns 4 rows every time. When you add the "(SELECT...)" clause to the UPDATE statement's WHERE clause, it says, "UPDATE this row if anything results from the '(SELECT...)' statement". Since we already agreed that 4 rows result from the SELECT clause, that certainly is enough to cause Oracle to update every row.

The problem comes from your not comparing data from each row you UPDATE to data that results from the SELECT clause. You need to correlate the rows you are updating with the rows in the SELECT clause.

Since I do not have your tables with which to test, I cannot guarantee the syntax, but here is what I suggest:

update RqDocuments b
set   RqDocuments.FileDirectory = 'c:\Projects'
     ,VersionNumber = VersionNumber + .0001
     ,VersionUserId = 99
     ,VersionDateTime = Sysdate
     ,VersionReason = 'RequistePro Document Update'
where exists (select 'x'
              from RqProjectDocuments A
              where A.DocumentId = B.Id and A.ProjectId = 12)
/

Let us know if this works for you.

Mufasa
(aka Dave of Sandy, Utah, USA @ 01:42 (29May04) UTC (aka "GMT" and "Zulu"), 18:42 (28May04) Mountain Time)

 

[BJCooperIT]

What a great explanation Dave. Have a star on me.

[sup]Beware of false knowledge; it is more dangerous than ignorance.[/sup][sup] ~George Bernard Shaw[/sup]
Systems Project Analyst/Custom Forms & PL/SQL - Oracle/Windows

 

[SantaMufasa]

BJ,

You are always so genuinely kind ! Thanks for your thoughtfulness.

Mufasa
(aka Dave of Sandy, Utah, USA @ 04:43 (29May04) UTC (aka "GMT" and "Zulu"), 21:43 (28May04) Mountain Time)

 

[cas85]

Hi Mufasa,

You explained the point well. I get it. My exits statement is alwasys true so I update every record. Could you explian what the value X should be? My guess would be b.ID

 

[SantaMufasa]

CAS,

Actually, the value of 'X' should be 'X' (Or any other non-null value is fine.) The "WHERE exists..." logic is basically a programming "trick". This may sound crazy until you understand that, effectively,
"...where exists (select 'x'
from RqProjectDocuments A
where A.DocumentId = B.Id and A.ProjectId = 12)"

...means:
"1) Correlate the inner SELECT condition with the outer UPDATE condition, then
2) if the correlating condition is TRUE for the current row to UPDATE, then
3) display a "flag" ('x') from the inner query, then
4) revert now to the outer UPDATE and check to see if a "flag" ('x') was raised by the inner query (i.e., did anything result from the inner SELECT), signaling that the UPDATE should occur for the current row, then
5) If anything resulted from the inner query, then perform the prescribed UPDATE for the current row.

If I really had done a good enough job explaining things in the first place, then I probably should have included this explanation. Did it make enough sense?

Mufasa
(aka Dave of Sandy, Utah, USA @ 07:00 (31May04) UTC (aka "GMT" and "Zulu"), 00:00 (31May04) Mountain Time)

 

[SantaMufasa]

...and one more clarification/correction: Item 1), above, should more properly read:

1) Correlate the inner SELECT condition using the outer UPDATE's current-row's value, "b.ID".

Sorry, it's late here.

Mufasa
(aka Dave of Sandy, Utah, USA @ 07:15 (31May04) UTC (aka "GMT" and "Zulu"), 00:15 (31May04) Mountain Time)

 

[cas85]

Hi Mufasa,

No problem with the lateness. I appreciate the help. I think I've got it.

I tried to implement the code you sent but it did not work. Did I miss something? See below:

update RqDocuments
set RqDocuments.FileDirectory = 'c:\Projects',VersionNumber = VersionNumber + .0001,VersionUserId = 99,
VersionDateTime = Sysdate, VersionReason = 'RequistePro Document Update'
where exists ( select b.id from Rqdocuments B, RqProjectDocuments A where A.DocumentId = B.Id and A.ProjectId = 12)

But I tried this and it did work:

update RqDocuments
set RqDocuments.FileDirectory = 'c:\Projects',VersionNumber = VersionNumber + .0001,VersionUserId = 99,
VersionDateTime = Sysdate, VersionReason = 'RequistePro Document Update'
where RqDocuments.id in (select B.id
from RqProjectDocuments A, RqDocuments B where A.DocumentId = B.Id and A.ProjectId = 12)

 

[SantaMufasa]

Cas,

1) The code that I posted on May 28 works fine. The code that you tried and failed with on June 1 is very different from the working code that I posted on May 28. Notice your correlated subquery in the failing code uses two tables, not the one table I used in the working May 28 correlated subquery.

2) The second code piece that you posted on June 1 that works....works. It is supposed to work. But it uses a different logic srategy ("IN") from "EXISTS", and they truly behave WAAAAAAAY differently from one another. Although one might think that they are similar, they would be mistaken if they do. Granted, "EXISTS" is more difficult to understand than "IN", but "EXISTS" always performs faster than comparable "IN" logic.

If you wish to followup to clarify the differences, I'm happy to do so.

Mufasa
(aka Dave of Sandy, Utah, USA @ 06:51 (03Jun04) UTC (aka "GMT" and "Zulu"), 23:51 (02Jun04) Mountain Time)

 

[datuk04]

Sorry to disturb, but from my point of view this code :

/*
update RqDocuments
set RqDocuments.FileDirectory = 'c:\Projects',VersionNumber = VersionNumber + .0001,VersionUserId = 99,
VersionDateTime = Sysdate, VersionReason = 'RequistePro Document Update'
where exists
(select A.ProjectId, A.DocumentId, B.id
from RqProjectDocuments A, RqDocuments B where A.DocumentId = B.Id and A.ProjectId = 12)
*/

and

/*
update RqDocuments b
set RqDocuments.FileDirectory = 'c:\Projects'
,VersionNumber = VersionNumber + .0001
,VersionUserId = 99
,VersionDateTime = Sysdate
,VersionReason = 'RequistePro Document Update'
where exists (select 'x'
from RqProjectDocuments A
where A.DocumentId = B.Id and A.ProjectId = 12)
/
*/

will give the same result. 'where exists' is a syntax to check whether it is true exist or not/false exist. From both SQL I found that both result is true exist. So the update command is executed.

I think that you only need to update 4 rows of your data only, don't you? So why don't you use syntax like this

/*
update RqDocuments
set RqDocuments.FileDirectory = 'c:\Projects'
,VersionNumber = VersionNumber + .0001
,VersionUserId = 99
,VersionDateTime = Sysdate
,VersionReason = 'RequistePro Document Update'
where id in
(select DocumentId
from RqProjectDocuments A
where ProjectId = 12)
*/

Even thought it is slow but it will give you the correct results.

 

[sem]

Datuk04, your point of view is erroneous, that query are completely different. The first one check whether

(select A.ProjectId, A.DocumentId, B.id 
from RqProjectDocuments A, RqDocuments B where A.DocumentId = B.Id and A.ProjectId = 12)

returns at least one record and if so updates ALL records in RqDocuments. Tables RqDocuments in subquery and update statement are independant, you may treat them as diferent tables containing the same data.

The second one checks whether

 (select 'x'
              from RqProjectDocuments A
              where A.DocumentId = B.Id and
              A.ProjectId = 12)

for EACH record in RqDocuments (correlated subquery), because in both parts you use the same table.


Regards, Dima

http://seminihin.narod.ru

 

[cas85]

Hi Mufasa,

Thanks for the help. I was rushed before but I see how your code works. Thanks!