Select qry works - delete qry fails

  • Permalink
  • submit to reddit
  • Email
  • Follow


Why does this select query return the correct records but when I make it a 
delete query I get a msgbox with "Could not delete from specified
tables".

   SELECT BMIDLog.*
FROM stageBMIDLog INNER JOIN BMIDLog ON (BMIDLog.BattID = stageBMIDLog.BattID)
AND (BMIDLog.VehicleID = stageBMIDLog.VehicleID) AND (BMIDLog.TotalChgAhs =
stageBMIDLog.TotalChgAhs)

   DELETE BMIDLog.*
FROM stageBMIDLog INNER JOIN BMIDLog ON (BMIDLog.BattID = stageBMIDLog.BattID)
AND (BMIDLog.VehicleID = stageBMIDLog.VehicleID) AND (BMIDLog.TotalChgAhs =
stageBMIDLog.TotalChgAhs)

Thanks

0
Reply ms3400 (28) 2/25/2004 3:20:32 AM

See related articles to this posting


I suspect it is because you've joined table together... I don't think you
can't delete records this way.

You should delete from one table and set up the relationships so the delete
will cascade to any associated tables.
-- 
Bradley
Software Developer www.hrsystems.com.au
A Christian Response www.pastornet.net.au/response

"ms" <ms@nospam.comcast.net> wrote in message
news:9t6dnWr1p8sPiaHdRVn-ug@comcast.com...
> Why does this select query return the correct records but when I make it a
> delete query I get a msgbox with "Could not delete from specified
> tables".
>
>    SELECT BMIDLog.*
> FROM stageBMIDLog INNER JOIN BMIDLog ON (BMIDLog.BattID =
stageBMIDLog.BattID)
> AND (BMIDLog.VehicleID = stageBMIDLog.VehicleID) AND (BMIDLog.TotalChgAhs
=
> stageBMIDLog.TotalChgAhs)
>
>    DELETE BMIDLog.*
> FROM stageBMIDLog INNER JOIN BMIDLog ON (BMIDLog.BattID =
stageBMIDLog.BattID)
> AND (BMIDLog.VehicleID = stageBMIDLog.VehicleID) AND (BMIDLog.TotalChgAhs
=
> stageBMIDLog.TotalChgAhs)
>
> Thanks
>


0
Reply bradley9026 (125) 2/25/2004 5:10:26 AM

Thanks Bradley, but won't confuses me is, why does this Delete query work then? 
   What makes a join query in a delete statement work?

DELETE stageChargeLog.*
FROM ChargeLog INNER JOIN stageChargeLog ON 
([ChargeLog].[STTIME]=[stageChargeLog].[STTIME]) AND 
([ChargeLog].[STDATE]=[stageChargeLog].[STDATE]) AND 
([ChargeLog].[BattID]=[stageChargeLog].[BattID]);


Bradley wrote:
> I suspect it is because you've joined table together... I don't think you
> can't delete records this way.
> 
> You should delete from one table and set up the relationships so the delete
> will cascade to any associated tables.

0
Reply ms3400 (28) 2/25/2004 6:36:18 PM

I also followed Access Help (Delete records from one table or tables in a 
one-to-one (and one-to-many) relationship) using the query design window and 
still it works as a select but fails as a Delete.  No information, no help, 
nothing.  Just says can't delete.
This is what the query design query is in SQL:

DELETE stageBMIDLog.*
FROM stageBMIDLog, TMPstageBMIDLog
WHERE TMPstageBMIDLog.TotalChgAhs=[stageBMIDLog].[TotalChgAhs] AND 
TMPstageBMIDLog.VehicleID=[stageBMIDLog].[vehicleid] AND 
TMPstageBMIDLog.BattID=[stageBMIDLog].[battid];


ms wrote:

> Thanks Bradley, but won't confuses me is, why does this Delete query 
> work then?   What makes a join query in a delete statement work?
> 
> DELETE stageChargeLog.*
> FROM ChargeLog INNER JOIN stageChargeLog ON 
> ([ChargeLog].[STTIME]=[stageChargeLog].[STTIME]) AND 
> ([ChargeLog].[STDATE]=[stageChargeLog].[STDATE]) AND 
> ([ChargeLog].[BattID]=[stageChargeLog].[BattID]);
> 
> 
> Bradley wrote:
> 
>> I suspect it is because you've joined table together... I don't think you
>> can't delete records this way.
>>
>> You should delete from one table and set up the relationships so the 
>> delete
>> will cascade to any associated tables.
> 
> 

0
Reply ms3400 (28) 2/25/2004 8:16:28 PM

I figured this out - enough to get my job done:
The delete query with a JOIN works when using .Execute from code vs. from the 
object window.
Don't know why though.
Thanks,
mark

ms wrote:
> I also followed Access Help (Delete records from one table or tables in 
> a one-to-one (and one-to-many) relationship) using the query design 
> window and still it works as a select but fails as a Delete.  No 
> information, no help, nothing.  Just says can't delete.
> This is what the query design query is in SQL:
> 
> DELETE stageBMIDLog.*
> FROM stageBMIDLog, TMPstageBMIDLog
> WHERE TMPstageBMIDLog.TotalChgAhs=[stageBMIDLog].[TotalChgAhs] AND 
> TMPstageBMIDLog.VehicleID=[stageBMIDLog].[vehicleid] AND 
> TMPstageBMIDLog.BattID=[stageBMIDLog].[battid];
> 
> 
> ms wrote:
> 
>> Thanks Bradley, but won't confuses me is, why does this Delete query 
>> work then?   What makes a join query in a delete statement work?
>>
>> DELETE stageChargeLog.*
>> FROM ChargeLog INNER JOIN stageChargeLog ON 
>> ([ChargeLog].[STTIME]=[stageChargeLog].[STTIME]) AND 
>> ([ChargeLog].[STDATE]=[stageChargeLog].[STDATE]) AND 
>> ([ChargeLog].[BattID]=[stageChargeLog].[BattID]);
>>
>>
>> Bradley wrote:
>>
>>> I suspect it is because you've joined table together... I don't think 
>>> you
>>> can't delete records this way.
>>>
>>> You should delete from one table and set up the relationships so the 
>>> delete
>>> will cascade to any associated tables.
>>
>>
>>
> 

0
Reply ms3400 (28) 2/25/2004 11:28:39 PM

I figured this out - enough to get my job done:
The delete query with a JOIN works when using .Execute from code vs. from the 
object window.
Don't know why though.
Thanks,
mark

ms wrote:
> I also followed Access Help (Delete records from one table or tables in 
> a one-to-one (and one-to-many) relationship) using the query design 
> window and still it works as a select but fails as a Delete.  No 
> information, no help, nothing.  Just says can't delete.
> This is what the query design query is in SQL:
> 
> DELETE stageBMIDLog.*
> FROM stageBMIDLog, TMPstageBMIDLog
> WHERE TMPstageBMIDLog.TotalChgAhs=[stageBMIDLog].[TotalChgAhs] AND 
> TMPstageBMIDLog.VehicleID=[stageBMIDLog].[vehicleid] AND 
> TMPstageBMIDLog.BattID=[stageBMIDLog].[battid];
> 
> 
> ms wrote:
> 
>> Thanks Bradley, but won't confuses me is, why does this Delete query 
>> work then?   What makes a join query in a delete statement work?
>>
>> DELETE stageChargeLog.*
>> FROM ChargeLog INNER JOIN stageChargeLog ON 
>> ([ChargeLog].[STTIME]=[stageChargeLog].[STTIME]) AND 
>> ([ChargeLog].[STDATE]=[stageChargeLog].[STDATE]) AND 
>> ([ChargeLog].[BattID]=[stageChargeLog].[BattID]);
>>
>>
>> Bradley wrote:
>>
>>> I suspect it is because you've joined table together... I don't think 
>>> you
>>> can't delete records this way.
>>>
>>> You should delete from one table and set up the relationships so the 
>>> delete
>>> will cascade to any associated tables.
>>
>>
>>
> 

0
Reply ms3400 (28) 2/25/2004 11:29:12 PM
comp.databases.ms-access 42298 articles. 10 followers. Post

5 Replies
91 Views

Similar Articles

[PageSpeed] 23


  • Permalink
  • submit to reddit
  • Email
  • Follow


Reply:

Similar Artilces:

SELECT works but UPDATE fails. ?
This statement fails update ded_temp a set a.balance = (select sum(b.ln_amt) from ded_temp b where a.cust_no = b.cust_no and a.ded_type_cd = b.ded_type_cd and a.chk_no = b.chk_no group by cust_no, ded_type_cd, chk_no) With this error: Server: Msg 170, Level 15, State 1, Line 1 Line 1: Incorrect syntax near 'a'. But this statement: select * from ded_temp a where a.balance = (select sum(b.ln_amt) from ded_temp b where a.cust_no = b.cust_no ...

Select qry dialog box
I have a select qry that prompts for an employees name when you open the qry. Is there a way to place a drop down in the dialog box that pops up requesting the name, so you can select the name from the drop down list Thanks in advance for all your help To do what you are asking, you need to create a form with the drop down information, then have the query refer to the value of the combo box on the form for its criteria instead of prompting you directly. -- Wayne Morgan "Bob McHale" <rmchale786@aol.com> wrote in message news:55d90fcc.0310021310.5402102c@posting.google.com...

select qry
Hi all, when we give ' select * from emp;' it gives an output. can you get the same output by putting some value in statement below select * from ********** where table_name='EMP'; Thanks, shij No. sim Hi, What exactly are you trying to achieve? You can say SELECT * from all_tables where table_name = 'EMP', but this wil give you the meta-data. If you say Select * from EMP, this gives you the data(application specific) that resides IN the EMP table. Probably if you elaborate your need, we'll be able to help you better. Thanks...

Select qry from linked SQL table in combobox, locks update.
Hello everyone This is my first post here. I'm working on a project at work and a new to db programming. I have a very specific issue I'm hoping you can help me with.. 1) --- Select query in combo box rowsource (from linked table) -- SELECT dbo_LOCLIST.LOCCODE, dbo_DLCOMMENTS.COMMENTS dbo_LOCLIST.DESCR, dbo_LOCLIST.INVADD1 FROM dbo_DLCOMMENTS LEFT JOI dbo_LOCLIST ON dbo_DLCOMMENTS.LOCCODE = dbo_LOCLIST.LOCCODE ORDER B dbo_LOCLIST.LOCCODE 2) --- Code to update table (based on a click event command button -- Dim mylw As Databas Set mylw = OpenDatabase("LWDB...

session_start() intermittently fails on the same page, then works, then works, then fails..
I am using session_start() on index.php and for some reason sometimes it'll fail. No warnings, no errors, no notices, not even after prepending error_reporting(E_ALL) and ini_set('display_errors', TRUE) before session_start() do I see anything, it just plain dies. I used LiveHTTPHeaders to verify that I am getting a 200 OK response, thus everything seems OK except that it's dying on session_start(). Sometimes. I'll go and refresh the page and then POOF everything is just fine, the page delivers. I'll refresh a few more times to test and then after 2 - 3 times it fa...

Sometimes "ls" works while "ls -v" fails; Sometimes "ls -v" works while "ls" fails; Sometimes both fail; Why???
Hi all, I am trying to port linux kernel 2.4.20 to a new powerpc Book E processor/chip. I used the 16550A UART driver (in drivers/char/serial.c) with IRQ = 0 (no interrupt). I have tried to use different values for rs_strobe_time and the kernel prints out messages correctly. My problem is that when I tried to do "ls" or "export" in user mode, sometimes strange characters (or incorrect content) print out. Anyone has any idea? I have been struggling with this problem for over 2 months. Here is the screen dump. This one showing a case where "ls -l" ...

Nodes Selected property fails to visually select
I set a Node's selected property to True and also set the Ensure Visible property to True , yet, when I see the Node in the TreeView the Node is not visually selected, i.e. there is no focus rectangle around it nor is its background highlighted as it would if I manually clicked the Node. Why is this? How can I force the Node to be visually selected as well when I set the .Selected property to True? I set the .Selected property like so: Me.trv.Nodes.Item(Me.cmdFields(iIndex%).Tag).Selected = True Me.trv.Nodes.Item(Me.cmdFields(iIndex%).Tag).EnsureVisible A check of the debugger sh...

"Make" works at home, fails at work.
I'm having a problem with make at work. I installed djgpp on my computer at work, trying to duplicate my home installation. I also copied over my personal libraries, source code, makefiles, etc. But when I try to "make" anything at work I get error "-1", except in the case of an empty rule. Even a simple makefile like this bombs out: blat: [tab]@echo splat When I type "make", what it SHOULD do is just print "splat". What it does instead is this: make.exe: *** [blat] Error -1 When I use an "empty" rule it works just fine: Makefile: ...

Qry strlen ?
Hi, we have strNcpy, strNcmp etc, (small n obviously), but why not in strlen ? the question is i am having some buffer strlen(buff); what happen if buff is not containing '\0', (i have try some prog. it will crashing/printing junk chars, using valgrind its showing invalid mem read etc..) so what i think is i made something simillar to above N-versioned of strlen which will check for strlen of buff for some MAX char, and return appropriate result. I want to ask that any simillar implementation already available ? C-Std says anything about this ? --raxit sheth In article <...

builder.node('select'..) selected not working solution
FYI, with something like this: hotelement = "hotelement"+hotcount; hotspot = Builder.node('div', { id:'hotspot'+hotcount, className:'hotspot', style:''+divstyle }, [ Builder.node('select', {id:hotelement, onChange:hotspotlink}, [ Builder.node('option',{value:'textfield', selected:''},"textfield"), Builder.node('option',{value:'textarea'},"textarea"), Builder.node('option',{value:'dropdown'},"dropdown"), Builder.node('...

SELECT not working???
Hi everyone, I have a table with 4 columns in it, ID,Category,Name,Keywords I am trying to select the entries that have keywords that are submitted from a form. However, it does not always find the entry. For example if I select 'agriculture' from the form, I get a blank result when I know that there is an entry for 'agriculture'..... I am using this code $query = sprintf ( "SELECT Category,Name FROM valley WHERE Keywords LIKE '%$search%' ORDER BY name LIMIT %d,%d", $start -...

VBA or qry
When using forms are you best to use SQL or run VB code to load the data from tables. Or is it just personal preference. Anyone that can shed any light on this, would be appreciated. TIA Stephen I don't understand the question. Bound forms will use a table, a query, or an SQL statement as their RecordSource. Under some conditions, it is possible to open a recordset to use as the RecordSource, but, in my experience, it is always just as easy to use the table, query, or SQL directly as RecordSource that you'd use to open the Recordset. The exception _may_ be the use of "disconne...

select not working?
I'm having some trouble using select in my program. I'm not sure if I should be asking here or in a unix newsgroup but I am on MacOS so... My code is below but for some reason select never returns anything other than 0... { // '_s' is a socket discriptor that is bound and listening. fd_set rfds; FD_ZERO( &rfds ); FD_SET( _s, &rfds ); timeval tv; tv.tv_sec = 0; tv.tv_usec = 0; int retval = select( 1, &rfds, 0, 0, &tv ); if ( retval ) return true; else return false; } I know the socket is set up...

labview dll fails to work while original VIs work perfectly
Hello, I have a little problem to which I have no clue. I have compiled a project to a DLL ant tested it with a test VI. While the test VI runs perfectly when calling other VIs it freezes when calling the DLL. Has anyone an idea ? It works like this. I start initializing the references to four ActiveX controls and then begin to dump video files on my disk and write the metadata in a database. Then I launch a VI that commands the process to stop when pressing on the stop button.<img src="http://img76.imageshack.us/img76/9046/vivsdllth2.png"> Thanks a lotMilas Oh and the databas...

One app FAILED to work on Performa, yet works in Classic mode why?
Stratego 0.95 has failed to work on any Mac I've owned that was not a native 68K beast. It failed to work at all on my Performa 6360 ranging from os 8.1-9.1, yet it worked okay under os 8.1 on my parents on Performa 6116 (I have no idea why), but failed to work after I installed 8.5 on their mac.. Just today I copied it over to my ibook from a old zip disk and it works okay under the classic mode of OSX Tiger, why? This makes no sense. It failed on my Performa 6360 many times and even failed when I booted from a cd and zip. Perhaps the Performa 6360 rom was a culprit or somethin...

Selected attribute for select control doesn't work in Firefox
Hello, I have the following select control: <SELECT name=3D"Currency"> <OPTION VALUE=3D"1">$-USD-US Dollar</OPTION> <OPTION VALUE=3D"2" SELECTED >=C2=A3-GBP-British Pound</OPTION> <OPTION VALUE=3D"3">=E2=82=AC-EUR-Euro</OPTION> <OPTION VALUE=3D"4">$-CAD-Canadian Dollar</OPTION> <OPTION VALUE=3D"5">$-AUD-Australian Dollar</OPTION> <OPTION VALUE=3D"6">$-NZD-New Zealand Dollar</OPTION> <OPTION VALUE=3D"7">$-MXN-Mexican Pesos</OPTI...

Script is working in IE, but not working in Netscape 7
I'm a beginner with Javascript and especially cross-browser Javascript. I got this working in IE, but not in Netscape 7. It seems like, in Netscape, every time I click on a button, the focus shifts to that button, so there is no text to be selected. What should I do? Below you'll see some code that I have in one of my forms. I was hoping to have these buttons and when I click on them they would take selected text from a textarea box and replace it with the text but surrounded with the HTML tags I wanted. But I can't get this to work. Why? <script language=&qu...

Boost Libraries failing to work with g++ 3.3.2, but working with 3.4.5
Code snippet: #include <iostream> #include <string> #include <boost/regex.hpp> // Boost.Regex lib using namespace std; int main( ) { std::string s, sre; boost::regex re; .... try { // Set up the regular expression for case-insensitivity re.assign(sre, boost::regex_constants::icase); } // catch (boost::regex_error& e) catch (...) { cout << sre << " is not a valid regular expression: \"" << endl; continue; } ... } This code snippet builds well with GCC g...

SELECT * works but SELECT FIELD1,FIELD2.. gives "supplied argument is not a valid MySQL result"
Hello People, here is a problem I don=B4t know how to solve. I would appreciate some clue for it. In all my tables I use normally SELECT FIELD1,FIELD2...FIELDn from table and never I got a single problem. But there is a script where I can=B4t solve this one. This table has a BLOB field with pictures, so when calling SELECT * from TABLE it works ok, picture comes and display beautiful. But when I call SELECT FIELD1..BLOB,..FIELDn from TABLE it produces a "supplied argument is not a valid MySQL result " at mysql_fetch_array() command. It shows error exactly that way, with empty par...

ERROR: Write to WORK.CURR_AGENTS.DATA failed. File is full and ma y be damaged message ? How do I increase work space please
People, I got the following error message today. Am I correct in assumming I ran out of space in my work area ? If I am correct how do I increase the space in my work area please? ERROR: Write to WORK.CURR_AGENTS.DATA failed. File is full and may be damaged Thanks Dave Dave Fickbohm Data Mining Analyst Homegain+ 2450 45th St. Emeryville, CA, 94608 Phone 510 655 0800 ext 4151 ...

Re: ERROR: Write to WORK.CURR_AGENTS.DATA failed. File is full and ma y be damaged message ? How do I increase work space please
I get this error, too. It's not just work space - this happens when the total available disk space is depleted. There may be efficiencies to implement in your work - can you subset the data you're using? Maybe break datasets in two before sorting the parts individually? Otherwise, the only way to avoid it is to free up space (delete files). I work on SAS in Windows - I can hit Refresh in Windows Explorer and watch the available space eroding away while the job runs - it's sad to see, actually. Good luck! Steve Raimi ...

ERROR: Write to WORK.CURR_AGENTS.DATA failed. File is full an d m a y be damaged message ? How do I increase work space please
Dear SAS-L-ers, In the thread to address David Fickbohm's Out of Space problem, Gerhard Hellriegel posted the following: > But be careful with that option: sometimes the compression > increases the space for datasets, especially if you have not > many variables (<8 or 10) or / and all variables are filled > up with different values. The compression algorithm is a simple one! > David, this is a good tip, and I see that you have already received some good advice on how to check for this problem by Ms. House. I want to add that, though SAS data set compression reduces da...

Re: ERROR: Write to WORK.CURR_AGENTS.DATA failed. File is full and ma y be damaged message ? How do I increase work space please
David, I have only gotten this error while working on the big iron, and yes it usually means I ran out of work space, or sort space, and if you are writing out to a file instead of a work data set, then it usually means you ran out of space in the file you are writing too (i.e. PDSE). HTH Toby Dunn -----Original Message----- From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of David Fickbohm Sent: Monday, September 20, 2004 1:01 PM To: SAS-L@LISTSERV.UGA.EDU Subject: ERROR: Write to WORK.CURR_AGENTS.DATA failed. File is full and ma y be damaged message ? How do I increase wo...

Re: ERROR: Write to WORK.CURR_AGENTS.DATA failed. File is full an d ma y be damaged message ? How do I increase work space please
steve, Thanks for the help. I solved the problem by turning on the compress option compress=yes. Dave -----Original Message----- From: Steve Raimi [mailto:steven.raimi@GM.COM] Sent: Tuesday, September 21, 2004 5:57 AM To: SAS-L@LISTSERV.UGA.EDU Subject: Re: ERROR: Write to WORK.CURR_AGENTS.DATA failed. File is full and ma y be damaged message ? How do I increase work space please I get this error, too. It's not just work space - this happens when the total available disk space is depleted. There may be efficiencies to implement in your work - can you subset the data you're using?...

Re: ERROR: Write to WORK.CURR_AGENTS.DATA failed. File is full and ma y be damaged message ? How do I increase work space please #2
On Tue, 21 Sep 2004 08:57:27 -0400, Steve Raimi <steven.raimi@GM.COM> wrote: >I get this error, too. It's not just work space - this happens when the >total available disk space is depleted. There may be efficiencies to >implement in your work - can you subset the data you're using? Maybe break >datasets in two before sorting the parts individually? Otherwise, the only >way to avoid it is to free up space (delete files). I work on SAS in >Windows - I can hit Refresh in Windows Explorer and watch the available >space eroding away while the job runs - it&...