Help with cleanup query [message #285128] |
Mon, 03 December 2007 12:07  |
aznewsh
Messages: 12 Registered: December 2007
|
Junior Member |
|
|
Here is what I am trying to do in plain English:
I have two tables, both tables have a 'uid' column, in fact that is the only column in table_for_compare.
The other column of interest is start_date, so here is my simplified table structure:
TABLE_NEEDING_CLEANUP
UID START_DATE
TABLE_FOR_COMPARE
UID
I want to delete all records from TABLE_NEEDING_CLEANUP where there is not a matching ID in TABLE_FOR_COMPARE AND (Not OR, the previous also has to be true) Start_Date < 2008
So in essence, any ID with a START_DATE => 2008 will never get deleted.
Here is my attempt at the SQL which does not work, it selects every record in the table:
SELECT COUNT(*) FROM TABLE_NEEDING_CLEANUP A WHERE NOT EXISTS
(SELECT 'X' FROM TABLE_FOR_COMPARE B WHERE A.UID = B.UID);
Thanks
|
|
|
|
|
|
|
|
Re: Help with cleanup query [message #285139 is a reply to message #285136] |
Mon, 03 December 2007 12:46   |
aznewsh
Messages: 12 Registered: December 2007
|
Junior Member |
|
|
I am already aware that I need to replace english with sql, if I knew how to do it, it would never have been posted as a problem in the first place - thanks anyway.
|
|
|
|
|
|
Re: Help with cleanup query [message #285143 is a reply to message #285140] |
Mon, 03 December 2007 13:01   |
aznewsh
Messages: 12 Registered: December 2007
|
Junior Member |
|
|
Thanks, but it still selects every row in the table which it shouldn't
I did actually run it like this in the first place, I just noticed the query above was not the right one - here is what I ran, I changed the last bit a little since START_DATE is a number field, (I didn't design the table)
SELECT COUNT(*) FROM TABLE1 A WHERE NOT EXISTS
(SELECT 'X' FROM TABLE2 B WHERE A.UID = B.UID
AND START_DATE < 2008);
What I don't understand is why it is selecting every row in the table?
So, I guess we have resolved that my query was right? in that case could it be a datatype issue since what the results seem to be suggesting is that no records in table1 match any records in table2 which is just wrong.
|
|
|
|
|
|
Re: Help with cleanup query [message #285152 is a reply to message #285146] |
Mon, 03 December 2007 14:15   |
aznewsh
Messages: 12 Registered: December 2007
|
Junior Member |
|
|
Please be sure to let me know where I have violated those guidelines.
I am no closer to a solution now than I was when I first posted, I had assumed this forum was a place to get help, not to be ridiculed.
Thank you to all those who tried to help, I will seek a solution elsewhere.
Kind Regards,
aznewsh
|
|
|
|
Re: Help with cleanup query [message #285161 is a reply to message #285159] |
Mon, 03 December 2007 15:25   |
aznewsh
Messages: 12 Registered: December 2007
|
Junior Member |
|
|
anacedent wrote on Mon, 03 December 2007 14:14 | >I will seek a solution elsewhere.
For a complete refund, please send your receipt showing your payment to dave.null@example.com
|
Don't recall asking for anything except help, but valiant attempt at a last word - have a great day.
|
|
|
Re: Help with cleanup query [message #285162 is a reply to message #285152] |
Mon, 03 December 2007 15:31   |
S.Rajaram
Messages: 1027 Registered: October 2006 Location: United Kingdom
|
Senior Member |
|
|
Can you post the count of the following query ?
select count(*) from table1, table2
where table1.uid = table2.uid
and
table2.start_date < 2008
Also from next time it will be more helpful if you could post the table structure.
Regards
Raj
|
|
|
Re: Help with cleanup query [message #285165 is a reply to message #285162] |
Mon, 03 December 2007 16:20   |
aznewsh
Messages: 12 Registered: December 2007
|
Junior Member |
|
|
The output is 0
Table Structure: (I am changing the table names and columns because they contain sensitive data which I am not comfortable posting across the internet - that said)
TABLE1
UID Varchar2(9)
START_DATE Number(4,0)
TABLE2
UID Varchar2(9)
UID would typically be of the form 001234567
START_DATE would typically be of the form 2008
I guess it is starting to look like some kind of data mismatch rather than a problem with the query?
The last query I ran did not get every row in the table but it did get most and after cross checking I found there were records selected that existied in both tables.
SELECT count(*) FROM TABLE1 A WHERE NOT EXISTS
(SELECT 'X' FROM TABLE2 B WHERE A.EMPLID = B.EMPLID)
AND START_YEAR < 2008;
Returned 4023
TABLE1 has 4200
TABLE2 has 21097
|
|
|
Re: Help with cleanup query [message #285168 is a reply to message #285165] |
Mon, 03 December 2007 16:31   |
aznewsh
Messages: 12 Registered: December 2007
|
Junior Member |
|
|
OK - I am stupid!
Found it finally
I had imported the uids without leading zeros and then ran an append to add them, later I did a rollback to correct a mistake and that also wiped out my leading zero change whci was not committed.
Thanks to all
|
|
|
Re: Help with cleanup query [message #285169 is a reply to message #285128] |
Mon, 03 December 2007 16:31   |
 |
BlackSwan
Messages: 26766 Registered: January 2009 Location: SoCal
|
Senior Member |
|
|
>>I will seek a solution elsewhere.
I see you are a person who always does what you say you will do.
>Please be sure to let me know where I have violated those guidelines.
From http://www.orafaq.com/forum/t/88153/0/
Quote: |
Provide DDL (CREATE TABLE, etc.) as appropriate instead of listing the table structure. It'll help people setting up a test set (yes, some people really do that)
Provide INSERT statements for sample data instead of pasting in or mocking up the results of a SELECT statement.
Provide your expected result set and explain the rules/reasons that lead to it.
|
Maybe you should be seeking assistance from some Professor or teaching assistant at NAU.
[Updated on: Mon, 03 December 2007 16:32] by Moderator Report message to a moderator
|
|
|
|
|
|
|
|
|
|
|
|
|
Re: Help with cleanup query [message #285427 is a reply to message #285128] |
Tue, 04 December 2007 09:12  |
aznewsh
Messages: 12 Registered: December 2007
|
Junior Member |
|
|
I think despite the fun little back and forth that ensued on this thread that this forum has the potential to help me in the future as I attempt to improve my SQL.
I am big enough to put these conflicts behind me and move on and continue to visit here, I apologize for my part for any offence caused. I hope others are big enough to take the same stance.
Regards
|
|
|