Ohio Chatter
Ohio Chatter

Forums

All Threads Serious Business Sports Politics Archive Pick 'ems
Log In Create Account

More Access/SQL Help Needed Please

Home ▸ Archive ▸ Serious Business ▸ More Access/SQL Help Needed Please
N

Nate

Formerly Known As Keebler

3,949 posts
Jun 24, 2010 2:42 PM
I need all entries from table Doc inserted into Destroyed where Destroyed_By1 is not blank. Also, I'd like to delete that entry from Doc after the insert..

Everything I've tried has failed. Any suggestions?
Jun 24, 2010 2:42pm
FatHobbit's avatar

FatHobbit

Senior Member

8,651 posts
Jun 24, 2010 3:52 PM
what table has Destroyed_By1?
Jun 24, 2010 3:52pm
N

Nate

Formerly Known As Keebler

3,949 posts
Jun 24, 2010 3:53 PM
Doc
Jun 24, 2010 3:53pm
FatHobbit's avatar

FatHobbit

Senior Member

8,651 posts
Jun 24, 2010 3:57 PM
try

INSERT INTO destroyed( field1, field2, field3)
SELECT field1, field2, field3
FROM DOC where Destroyed_By1<>"";

delete from DOC where Destroyed_By1<>"";
Jun 24, 2010 3:57pm
N

Nate

Formerly Known As Keebler

3,949 posts
Jun 24, 2010 4:01 PM
This is what I tried earlier and it didn't work.

INSERT INTO Destroyed (ID, Rack, Shelf, Stored_Date, Description, From_Date, To_Date, Destroy_Date, Placed_By1, Placed_By2, Destroy_By1, Destroy_By2)
SELECT ID, Rack, Shelf, Stored_Date, Description, From_Date, To_Date, Destroy_Date, Placed_By1, Placed_By2, Destroy_By1, Destroy_By2
FROM Doc WHERE Destroy_By1<> '';
Jun 24, 2010 4:01pm
FatHobbit's avatar

FatHobbit

Senior Member

8,651 posts
Jun 24, 2010 4:11 PM
Does it work if you just run the select? What error message do you get?
Jun 24, 2010 4:11pm
thePITman's avatar

thePITman

Senior Member

3,867 posts
Jun 24, 2010 4:13 PM
Why did it fail? What kind of error did you get? Here is what I would do in SQL:

INSERT INTO Destroyed (field1, field2)
SELECT field1, field2
FROM Doc
WHERE Destroy_By1 IS NOT NULL;

DELETE Doc
WHERE Destroy_By1 IS NOT NULL;


OR here is some PL/SQL that will delete each record as it is entered into the new table, instead of inserting all and then deleting all...


DECLARE

CURSOR c_get_blank_recs
IS
SELECT ID, field2, field3
FROM Doc
WHERE Destroy_By1 IS NOT NULL;

BEGIN

FOR r_get_blank_recs IN c_get_blank_recs LOOP
INSERT INTO Destroyed (ID, field2, field3)
VALUES r_get_blank_recs;

DELETE Doc WHERE ID = r_get_blank_recs.ID;
END LOOP;

COMMIT;

END;
Jun 24, 2010 4:13pm
N

Nate

Formerly Known As Keebler

3,949 posts
Jun 24, 2010 4:15 PM
Select runs fine.

Right now, it isn't giving me an error. Just nothing moving over into the Destroyed table.
Jun 24, 2010 4:15pm
FatHobbit's avatar

FatHobbit

Senior Member

8,651 posts
Jun 24, 2010 4:15 PM
thePITman;400273 wrote:Why did it fail? What kind of error did you get? Here is what I would do in SQL:

INSERT INTO Destroyed (field1, field2)
SELECT field1, field2
FROM Doc
WHERE Destroy_By1 IS NOT NULL;

Good point. The field might be null and not blank. If you try running the select without the insert, you'll see exactly what it is you're trying to insert.
Jun 24, 2010 4:15pm
FatHobbit's avatar

FatHobbit

Senior Member

8,651 posts
Jun 24, 2010 4:16 PM
Keebler;400279 wrote:Select runs fine.

Right now, it isn't giving me an error. Just nothing moving over into the Destroyed table.

Does it return anything? If it does, I would think that it would insert it or give an error.
Jun 24, 2010 4:16pm
thePITman's avatar

thePITman

Senior Member

3,867 posts
Jun 24, 2010 4:18 PM
FatHobbit;400280 wrote:Good point. The field might be null and not blank. If you try running the select without the insert, you'll see exactly what it is you're trying to insert.

Technically, the two comparisons of <> '' and IS NOT NULL are the same, but depending on the data type, <> '' may not be acceptable. In almost all cases I go with the IS [NOT] NULL instead of <> [or =] ''.

If Keebler can execute an anonymous PL/SQL block, it'd be easy.
FatHobbit;400286 wrote:Does it return anything? If it does, I would think that it would insert it or give an error.

Agreed.
Jun 24, 2010 4:18pm
N

Nate

Formerly Known As Keebler

3,949 posts
Jun 24, 2010 4:19 PM
Fuckin' a. I got it. It is correct. Fucking Office 2007 and its security content fucking AGAIN! How many times do I have to enable this shit?
Jun 24, 2010 4:19pm
N

Nate

Formerly Known As Keebler

3,949 posts
Jun 24, 2010 4:20 PM
<> '' worked.
Jun 24, 2010 4:20pm
thePITman's avatar

thePITman

Senior Member

3,867 posts
Jun 24, 2010 4:21 PM
Keebler;400294 wrote:Fuckin' a. I got it. It is correct. Fucking Office 2007 and its security content fucking AGAIN! How many times do I have to enable this shit?
Keebler;400295 wrote:<> '' worked.

Haha! Congrats again. Document it. Self-help (tier 0 support) is always the best and quickest option... but we don't mind, either! ;-)
Jun 24, 2010 4:21pm
N

Nate

Formerly Known As Keebler

3,949 posts
Jun 24, 2010 4:25 PM
My problem is I get it right early on but forget about the content blocker thing in Access 2007 and I think its wrong so I try everything else. LOL
Jun 24, 2010 4:25pm
FatHobbit's avatar

FatHobbit

Senior Member

8,651 posts
Jun 24, 2010 4:31 PM
Good deal. :)
Jun 24, 2010 4:31pm