• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 233
  • Last Modified:

SQL Where Select statement not applying

Hello

My SQL query as below

UPDATE SUPPORTING_DOCUMENT SET file_data = NULL
WHERE EXISTS (SELECT application_id
FROM SUPPORTING_DOCUMENT JOIN APPLICATION
ON APPLICATION.ID = SUPPORTING_DOCUMENT.application_id
WHERE APPLICATION.submission_date <= '10/01/2005 12:00:00 AM')

Should only be acting on one result (that meet the submission_date <= 10/01/2005) but it does not - it acts on the whole table and Updates every records to be NULL in the file_data directory..

The select statement

SELECT application_id
FROM SUPPORTING_DOCUMENT JOIN APPLICATION
ON APPLICATION.ID = SUPPORTING_DOCUMENT.application_id
WHERE APPLICATION.submission_date <= '10/01/2005 12:00:00 AM'

On its own works fine and selects the correct results.. But the WHERE clause does not seem to be running the select to find out which records to apply the NULL to..

I've obviously got something wrong in the syntax... can someone please tell me where I am going wrong?

Thanks!
0
wct296
Asked:
wct296
  • 2
  • 2
2 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you should use this syntax:

UPDATE SUPPORTING_DOCUMENT SET file_data = NULL
WHERE EXISTS (SELECT application_id
    FROM APPLICATION
    WHERE APPLICATION.ID = SUPPORTING_DOCUMENT.application_id
    AND APPLICATION.submission_date <= '10/01/2005 12:00:00 AM'
)
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
to explain the problem:
your subquery :
SELECT application_id
FROM SUPPORTING_DOCUMENT JOIN APPLICATION
ON APPLICATION.ID = SUPPORTING_DOCUMENT.application_id
WHERE APPLICATION.submission_date <= '10/01/2005 12:00:00 AM'

will return data, and hence the exists returns true for all the rows of SUPPORTING_DOCUMENT.

my above suggestion will make the subquery correlated, so, per row of SUPPORTING_DOCUMENT, will evaluate the subquery...
0
 
wct296Author Commented:
Thank you Angelll

So as long as ONE result set from my select occurs it sets the Where clause to 'true' and hence it processes all.. is that a correct assumption?
0
 
LowfatspreadCommented:
yes...
since you hadn't co-related the subquery to the naib query...

ie you had "another " join in your initial statement...
rather than processing the document table rows and having each of them checked for an associated condition
on the application table.
0
 
wct296Author Commented:
Thank you - I had another look at the provided script and worked out the logic behind it

Thanks
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now