Archive Quick SQL Help - Ohio Chatter

N

Nate

Posts: 3,949

Jun 18, 2010 4:32pm

INSERT INTO Bellevue_Pending
SELECT *
FROM Bellevue_Doc
WHERE Destroy_Date<"06/16/10";

Does this look right? I want to take any entry dated before 06/16/10 and insert it into the pending table.

Also, how can I automatically set 06/16/10 to be today's date?

I'm having total brain failure today and Google hasn't been much help.

Thanks.

justincredible

Posts: 32,056

Jun 18, 2010 4:36pm

Keebler;394415 wrote:INSERT INTO Bellevue_Pending
SELECT *
FROM Bellevue_Doc
WHERE Destroy_Date<"06/16/10";

Does this look right? I want to take any entry dated before 06/16/10 and insert it into the pending table.

Also, how can I automatically set 06/16/10 to be today's date?

I'm having total brain failure today and Google hasn't been much help.

Thanks.

That looks right.

Then you can do an UPDATE table SET Destroy_Date = CURDATE();

justincredible

Posts: 32,056

Jun 18, 2010 4:37pm

You could also alter that update if you only wanted to update certain rows, that query I gave would update all rows.

N

Nate

Posts: 3,949

Jun 18, 2010 4:49pm

What I'm trying to do is pull all records with a date before the current date for Destroy_Date and put them into a Pending Table. I don't need to update records, just run the query to move entries from one table to another based on the Destroy_Date being before the actual date.

justincredible

Posts: 32,056

Jun 18, 2010 5:06pm

Keebler;394433 wrote:What I'm trying to do is pull all records with a date before the current date for Destroy_Date and put them into a Pending Table. I don't need to update records, just run the query to move entries from one table to another based on the Destroy_Date being before the actual date.

But you said this:

Also, how can I automatically set 06/16/10 to be today's date?

I would just do that part with an update.

justincredible

Posts: 32,056

Jun 18, 2010 5:09pm

Or you could do something like:

INSERT INTO Bellevue_Pending (col, col, col, col, Destroy_Date)
SELECT col, col, col, col, CURDATE()
FROM Bellevue_Doc
WHERE Destroy_Date<"06/16/10";

justincredible

Posts: 32,056

Jun 18, 2010 5:10pm

That would set all of the columns of Bellevue_Pending to those of Bellevue_Doc except for Destroy_Date, which it would set to the current date.

Thunder70

Posts: 748

Jun 18, 2010 5:11pm

nerds...

justincredible

Posts: 32,056

Jun 18, 2010 5:13pm

Thunder70;394453 wrote:nerds...

You shut up, you!

N

Nate

Posts: 3,949

Jun 18, 2010 5:41pm

I guess I'm making this more complicated than it sounds.

WHERE Destory_Date < CURRENTDATE is what I want. So I want all records out of Bellevue_Doc where the date in Destroy_date is before the current date and insert those records where the destroy_date is before the current date into Bellevue_Pending. Nothing needs updated with the current date.

thePITman

Posts: 3,867

Jun 18, 2010 6:34pm

justincredible;394450 wrote:Or you could do something like:

INSERT INTO Bellevue_Pending (col, col, col, col, Destroy_Date)
SELECT col, col, col, col, CURDATE()
FROM Bellevue_Doc
WHERE Destroy_Date<"06/16/10";

This looks like it should work for you. If you want anything with a Destroy_Date of before "right now" then "Destroy_Date < SYSDATE" would work. However, if you want any Destroy_Date before "today", you will want "Destroy_Date < TRUNC(SYSDATE)" to get rid of the timestamp.

If you have the ability to do a little PL/SQL and run a standalone procedure, it'd be super-easy. Plus you can do more manipulation if you wanted, such as changing the date.

DECLARE

CURSOR get_doc_recs
IS
SELECT * FROM Bellevue_Doc
WHERE Destroy_Date < TRUNC(SYSDATE);

BEGIN

FOR c_doc_recs IN get_doc_recs
LOOP
c_doc_recs.Destroy_Date := SYSDATE; -- Sets Destroy_Date to "right now"

INSERT INTO Bellevue_Pending
VALUES c_doc_recs;
END LOOP;

END;

justincredible

Posts: 32,056

Jun 18, 2010 7:17pm

Keebler;394478 wrote:I guess I'm making this more complicated than it sounds.

WHERE Destory_Date < CURRENTDATE is what I want. So I want all records out of Bellevue_Doc where the date in Destroy_date is before the current date and insert those records where the destroy_date is before the current date into Bellevue_Pending. Nothing needs updated with the current date.

Gotcha. The way you worded it confused me with what you actually wanted.

INSERT INTO Bellevue_Pending
SELECT *
FROM Bellevue_Doc
WHERE Destroy_Date<CURDATE();

thePITman

Posts: 3,867

Jun 18, 2010 7:56pm

justincredible,
is CURDATE() the SQL Server version of SYSDATE? If so, would he need to use TRUNC(CURDATE()) if he wanted the previous day? I guess it matters what format Destroy_Date is being saved. If it does not have a time stamp, then it doesn't make a difference.

N

Nate

Posts: 3,949

Jun 19, 2010 2:38am

This would be SQL inside of Access if this makes things a little bit easier.
My insert statement wasn't working when I left when I hard coded a date in there. I don't know what I'm doing wrong. I haven't done SQL in over a year and its amazing how much rust I've gotten on it.

justincredible

Posts: 32,056

Jun 19, 2010 10:06am

My syntax is for MySQL.

N

Nate

Posts: 3,949

Jun 19, 2010 10:07am

I finally got it to work. My SQL was correct. There was just some gay Windows 7/Office 2007 Content Adviser that was blocking me from Running my query. Once I took that off it worked.

Thanks for all the help Justin and PITman

justincredible

Posts: 32,056

Jun 19, 2010 10:25am

Keebler;394810 wrote:I finally got it to work. My SQL was correct. There was just some gay Windows 7/Office 2007 Content Adviser that was blocking me from Running my query. Once I took that off it worked.

Thanks for all the help Justin and PITman

A Microsoft product gay? Who knew?

N

Nate

Posts: 3,949

Jun 19, 2010 10:38am

justincredible;394821 wrote:A Microsoft product gay? Who knew?

LOL Exactly.

Do you know a way to convert Date() from DD/MM/YYYY to MM/DD/YYYY? This is annoying the shit out of me. It's not like I am in fucking Europe.

thePITman

Posts: 3,867

Jun 21, 2010 12:32pm

I am not sure if you can do this in Access, but here at work (SQL w/ Oracle) I would just do TO_CHAR(CURDATE(),'MM/DD/YYYY')

N

Nate

Posts: 3,949

Jun 21, 2010 4:05pm

I got it to work with this.
SELECT Bellevue_Doc.Destroy_Date, *
FROM Bellevue_Doc
WHERE (((Bellevue_Doc.Destroy_Date)<Format(Date(),"mm/dd/yyyy")));

N

Nate

Posts: 3,949

Jun 21, 2010 4:47pm

I have a new question!!! ACCESS!!!

I have a form that works like this

Text Box 1 : ID
Text Box 2 : Destroy 1
Text Box 3 : Destroy 2

I need to take those 3 and insert into the Destroyed Table. After that I need that entry updated by the ID through the Doc table. After it is updated, I need the entry deleted out of the Doc table.

Any help would be appreciated.

N

Nate

Posts: 3,949

Jun 21, 2010 4:49pm

Access will entry those 3 into the table fine. I just need a query to run after that, that will update the entry and then delete the entry.

UPDATE Destroyed
WHERE .....Doc.ID = Destroyed.ID

DELETE * FROM Doc WHERE Doc.ID = Destroyed.ID

I'm sure I'm off but something close to that.

Apple

Posts: 2,620

Jun 21, 2010 5:31pm

Geeks I tell you...