Home » SQL & PL/SQL » SQL & PL/SQL » Help with cleanup query
Help with cleanup query [message #285128] Mon, 03 December 2007 12:07 Go to next message
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 #285130 is a reply to message #285128] Mon, 03 December 2007 12:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You want to delete rows that not exist in the other table and start_date is before 2008.
Just write the sentence with SQL words.

Regards
Michel
Re: Help with cleanup query [message #285132 is a reply to message #285130] Mon, 03 December 2007 12:20 Go to previous messageGo to next message
aznewsh
Messages: 12
Registered: December 2007
Junior Member
That's what I am trying to do, I pasted my attempt
Re: Help with cleanup query [message #285133 is a reply to message #285132] Mon, 03 December 2007 12:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You want to delete not count.
You have NOT EXIST, add year < ...
Where is the problem, where are you stuck?

Regards
Michel
Re: Help with cleanup query [message #285135 is a reply to message #285133] Mon, 03 December 2007 12:28 Go to previous messageGo to next message
aznewsh
Messages: 12
Registered: December 2007
Junior Member
the select count is just being careful before running the actual delete

I don't know where I am stuck, that is why I am asking for guidance
Re: Help with cleanup query [message #285136 is a reply to message #285135] Mon, 03 December 2007 12:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Take the sentence, replace all english word by SQL words and you have your delete.

By the way, delete is safe until you commit as you can always roll back.

Regards
Michel
Re: Help with cleanup query [message #285139 is a reply to message #285136] Mon, 03 December 2007 12:46 Go to previous messageGo to next message
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 #285140 is a reply to message #285136] Mon, 03 December 2007 12:47 Go to previous messageGo to next message
cmerry
Messages: 109
Registered: November 2005
Location: Idaho
Senior Member
Michel is a terrific dancer...dancing all around the solution, but sometimes it just saves time giving away the answers Wink

SELECT COUNT(*) FROM TABLE_NEEDING_CLEANUP A WHERE NOT EXISTS 
(SELECT 'X' FROM TABLE_FOR_COMPARE B WHERE A.UID = B.UID
AND start_date < TO_DATE('01-JAN-2008', 'DD-MON-YYYY'));

Re: Help with cleanup query [message #285141 is a reply to message #285128] Mon, 03 December 2007 12:54 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>but sometimes it just saves time giving away the answers
When you give a man a fish, he eats for today.
When you TEACH a man to fish, he can feed himself for life!
CMERRY, you've done nobody any big favor!
Re: Help with cleanup query [message #285142 is a reply to message #285140] Mon, 03 December 2007 12:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Cmerry, I didn't want to save time otherwise I posted the query that took half a second to find.
I wanted him to find and write it.
What do you think it happens now?
He has his query.
He don't know how to build the next one.
Great! Hope you will be there to write all the queries he will need in the future.

Regards
Michel
Re: Help with cleanup query [message #285143 is a reply to message #285140] Mon, 03 December 2007 13:01 Go to previous messageGo to next message
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 #285144 is a reply to message #285143] Mon, 03 December 2007 13:05 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
You need the DATE condition on the outside query.
Re: Help with cleanup query [message #285145 is a reply to message #285143] Mon, 03 December 2007 13:05 Go to previous messageGo to next message
aznewsh
Messages: 12
Registered: December 2007
Junior Member
Please don't talk about me like a child, I write almost all my own queries and will only post when really stuck.
Re: Help with cleanup query [message #285146 is a reply to message #285128] Mon, 03 December 2007 13:26 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
aznewsh,
http://www.orafaq.com/forum/t/88153/0/
Please read & FOLLOW posting guidelines as stated in URL above.
Re: Help with cleanup query [message #285152 is a reply to message #285146] Mon, 03 December 2007 14:15 Go to previous messageGo to next message
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 #285159 is a reply to message #285128] Mon, 03 December 2007 15:14 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>I will seek a solution elsewhere.
For a complete refund, please send your receipt showing your payment to dave.null@example.com
Re: Help with cleanup query [message #285161 is a reply to message #285159] Mon, 03 December 2007 15:25 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
BlackSwan
Messages: 25050
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 #285173 is a reply to message #285169] Mon, 03 December 2007 16:57 Go to previous messageGo to next message
aznewsh
Messages: 12
Registered: December 2007
Junior Member
Wow, you must be really bored!

Congratulations on your ability with a search engine.
Re: Help with cleanup query [message #285184 is a reply to message #285173] Mon, 03 December 2007 18:53 Go to previous messageGo to next message
cmerry
Messages: 109
Registered: November 2005
Location: Idaho
Senior Member
Can't we all just get along...

You'll have to forgive my tendency to give too much information. In this case, it was obvious the poster was not understanding the direction he was being lead. I cannot think of a single technical book that teaches without examples. And for that matter, I do not see any real teaching being done in this particular post. The OP was told what to do; he just did not understand the guidance.

And for the record, the OP discovered on his own that the query was not the problem; it was data. And I was not there holding anybody's hand.
Re: Help with cleanup query [message #285244 is a reply to message #285184] Tue, 04 December 2007 01:27 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Great. Another topic in the Newbies section where everybody is so wonderful and smart. Didn't we all start as a Newbie?

The OP got what he wanted. I didn't.

MHE

[Updated on: Tue, 04 December 2007 01:28]

Report message to a moderator

Re: Help with cleanup query [message #285289 is a reply to message #285244] Tue, 04 December 2007 03:40 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What did you want?
Re: Help with cleanup query [message #285327 is a reply to message #285128] Tue, 04 December 2007 04:38 Go to previous messageGo to next message
mr.rajeshyadav
Messages: 48
Registered: November 2007
Member
Try this...

SELECT COUNT(UID) FROM TABLE_NEEDING_CLEANUP WHERE uid NOT IN (SELECT distinct uid FROM TABLE_FOR_COMPARE)

[Updated on: Tue, 04 December 2007 04:54]

Report message to a moderator

Re: Help with cleanup query [message #285333 is a reply to message #285128] Tue, 04 December 2007 04:46 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member


@ Rajesh :

You query wont work ; It will result in syntax error .

Techonthenet link

Thumbs Up
Rajuvan.
Re: Help with cleanup query [message #285342 is a reply to message #285333] Tue, 04 December 2007 05:01 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
rajavu1 wrote on Tue, 04 December 2007 10:46


@ Rajesh :

You query wont work ; It will result in syntax error .


Thumbs Up
Rajuvan.

Are you sure about that? (I'm not saying that it will give the right answer, but are you sure that it will result in a syntax error?
Re: Help with cleanup query [message #285344 is a reply to message #285128] Tue, 04 December 2007 05:04 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Haha .. Smile it could have given syntax error.


His query was

SELECT COUNT(UID) FROM TABLE_NEEDING_CLEANUP WHERE uid NOT EXISTS(SELECT distinct uid FROM TABLE_FOR_COMPARE)



When i posted the query.. Now its updated

Try this...

Quote:

SELECT COUNT(UID) FROM TABLE_NEEDING_CLEANUP WHERE uid NOT IN (SELECT distinct uid FROM TABLE_FOR_COMPARE)


[Updated on: Tue, 04 December 2007 16:24]


Thumbs Up
Rajuvan.

[Updated on: Tue, 04 December 2007 05:06]

Report message to a moderator

Re: Help with cleanup query [message #285347 is a reply to message #285344] Tue, 04 December 2007 05:05 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
Fair play, as I posted my message I had a feeling that that might have been the case. And in fact, If I had looked at the repective post and edited times I could've confirmed that. Sorry Rajuvan.

[Updated on: Tue, 04 December 2007 05:06]

Report message to a moderator

Re: Help with cleanup query [message #285348 is a reply to message #285128] Tue, 04 December 2007 05:07 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

No probs ..

It could have been a nice catch otherwise Smile

Thumbs Up
Rajuvan.

[Updated on: Tue, 04 December 2007 05:08]

Report message to a moderator

Re: Help with cleanup query [message #285427 is a reply to message #285128] Tue, 04 December 2007 09:12 Go to previous message
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
Previous Topic: Urgent Help for 4 Shift Attendance procedure.
Next Topic: Merge columns
Goto Forum:
  


Current Time: Sun Dec 11 07:55:52 CST 2016

Total time taken to generate the page: 0.04422 seconds