|
|
delete record from table a if it matches record in table b
I'm having trouble getting this to work:
delete A.* from A where exists(select * from A, B
where a.empno = b.a_empno and a.paycode = b.a_paycode and a.hours1 =
b.a_hours1 and a.date1 = b.a_date1 and a.pos = b.a_pos);
It has to match all fields in both tables not just one. This is giving me
all of them, not just the matches.
Thanks for any ideas.
|
|
0
|
|
|
|
Reply
|
 Utf
|
2/7/2008 3:58:01 PM |
|
On Thu, 7 Feb 2008 07:58:01 -0800, denise <denise@discussions.microsoft.com>
wrote:
>I'm having trouble getting this to work:
>delete A.* from A where exists(select * from A, B
>where a.empno = b.a_empno and a.paycode = b.a_paycode and a.hours1 =
>b.a_hours1 and a.date1 = b.a_date1 and a.pos = b.a_pos);
>It has to match all fields in both tables not just one. This is giving me
>all of them, not just the matches.
>Thanks for any ideas.
If you create a unique Index on the combination of all five fields, then you
should be able to:
DELETE A.*
FROM A INNER JOIN B
ON a.empno = b.a_empno and a.paycode = b.a_paycode
and a.hours1 = b.a_hours1 and a.date1 = b.a_date1 and a.pos = b.a_pos;
What are you trying to accomplish? I wonder if a UNION query or an Append
might be a better option!
John W. Vinson [MVP]
|
|
|
0
|
|
|
|
Reply
|
John
|
2/7/2008 6:03:54 PM
|
|
|
1 Replies
201 Views
(page loaded in 0.044 seconds)
Similiar Articles: Help with using command button to delete a record from a table ...Help with using command button to delete a record from a table ... This does ALMOST ... Hi, I want to add a command button to a form that will delete all records in a table ... Delete duplicates in table - SQL? - microsoft.public.access ...It's pretty easy IF you have a primary key assigned for that table AND you really, really know which records that you don't want to keep. For example you define a ... delete query - could not delete from specified table - microsoft ...Hello, i am trying to delete (in delete query) records in a table. ... only those rows with matches will ... It does not delete the record from the primary table. ... Use Form button to move current record to another table ...... is to move this record to an archive table, and delete that ... don't need to move records ... Print report which matches current record on open form - microsoft ..... table I ... Delete record using vba - microsoft.public.accessI have a table whose primary key is text (length 50). I need to programmatically delete one record. What is the best way, just use DoCmd.RunSQL or... using vba to update record in another table - microsoft.public ...MS Access Copy record to another table based on a form ... the Flow of Word ... How to: Insert, Update, and Delete Records From a Table Using ... VBA ... When you are ... Can't delete record in subform - microsoft.public.access ...... delete any record on subform A, the computer hang.but when I remove subform B from my form, I can delete. the relationship between these 2 table ... still delete records ... Add an incremental field to an existing table - microsoft.public ...... You can then delete the autonumber column from the table and ... There are 13K+ records in table A. ... have added a field [LineNum] to Table B ... change existing table record ... Duplicating records from one table to itself - microsoft.public ...Delete one or more records from an Access database - Access ... So adding a record to one table does ... Delete all records from a table (but not the table itself) ... Clear the subform - microsoft.public.access.forms... with the RecordSource of the subform (a table). The ... I have a main form frmFoo to edit records in tblFoo. ... Can't delete record in subform - microsoft.public.access ... SQL: DELETE Statement - TechOnTheNet.comThe DELETE statement allows you to delete a single record or multiple records from a table. Delete Record From Table A that Is Not In Table B - Sql ServerDelete Record From Table A that Is Not In Table B. I have two tables; Table A id, name 101, jones 102, smith 103, williams 104, johnson 105, brown 106, green 107 ... Delete one or more records from an Access database - Access ...Rule 2: You can't delete a record from a primary table if matching records exist in a related table. For example, you can't delete an employee record from the Employees ... exam 3 flashcards | Quizlet - Flash cards, vocabulary memorization ...displays only the data that matches the query selection ... If there are related records in a related table, you can't delete a record in the primary table until you ... Delete records from one table that match record from another tableI have two files that I need to delete records from one file if the course code field matches. I'm trying to do this through a delete query. When I run the ... 7/22/2012 2:19:52 AM
|
|
|
|
|
|
|
|
|
Search |
Post Question |
Groups |
Stream |
About |
Register
16 followers. 