deleting records

[sam]

I have the following 2 tables
Table A
01 02 03 04 05 06 07
08 09 10 11 12 13 14
15 16 17 18 19 20 21
30 32 34 39 45 47 50
80 81 85 87 89 90 91

Table A has 5 records.

Table B
01 02 03 04 05 06 07
15 16 17 18 19 20 21
51 53 55 56 58 59 60
31 33 35 36 38 70 71
80 81 85 87 89 90 91

New records are not added in Table A, they are added in
Table B.
Table A and Table B's 3 records are the same. I want
records of Table A that are same in Table B to be deleted
from Table A.
When deleted the total number of records in Table A
should not increase but decrease, for eg in the above
tables 3 records are the same so when deleted Table A
should have only 2 records left.

I would really appreciate for your help
Thank you

 

[Jamie Richards]

I have no idea what those values represent, but I"ll have a crack at
answering your question anyway.

You must have a unique key (ID) in each table which can be joined, enabling
an exact match of rows. If you have, you could try:

DELETE *
FROM [Table A]
WHERE [Table A].ID = [Table B].ID

You didn't say what RDMS you're using, but the above should work in Access.
If you're using SQL server you can omit the " * " from the statement.


Jamie

 

[Jamie Richards]

First I say you ned a key to join on then I don't even use it!

This may work better:

DELETE *
FROM [Table A] AS T1 INNER JOIN [Table B] AS T2 ON T1.ID = T2.ID
WHERE [Table A].ID = [Table B].ID


Jamie

Folks, don't bother with the "clever" virus infected emails you are sending.
All Unsolicited email is destroyed.

I have no idea what those values represent, but I"ll have a crack at
answering your question anyway.

You must have a unique key (ID) in each table which can be joined,
enabling an exact match of rows. If you have, you could try:

DELETE *
FROM [Table A]
WHERE [Table A].ID = [Table B].ID

You didn't say what RDMS you're using, but the above should work in
Access. If you're using SQL server you can omit the " * " from the
statement.


Jamie

I have the following 2 tables
Table A
01 02 03 04 05 06 07
08 09 10 11 12 13 14
15 16 17 18 19 20 21
30 32 34 39 45 47 50
80 81 85 87 89 90 91

Table A has 5 records.

Table B
01 02 03 04 05 06 07
15 16 17 18 19 20 21
51 53 55 56 58 59 60
31 33 35 36 38 70 71
80 81 85 87 89 90 91

New records are not added in Table A, they are added in
Table B.
Table A and Table B's 3 records are the same. I want
records of Table A that are same in Table B to be deleted
from Table A.
When deleted the total number of records in Table A
should not increase but decrease, for eg in the above
tables 3 records are the same so when deleted Table A
should have only 2 records left.

I would really appreciate for your help
Thank you

 

[Jamie Richards]

Not to make myself look stupid or anything, but here's another thought.
Let's face it, you can't afford to stuff up delete queries.

If you want to delete all records from Table1, you can use:

DELETE Table1.*
FROM Table1 INNER JOIN Table2 ON Table1.ID = Table2.ID

This works...I promise.

If you wanted only certain records, you can specify the crieria in the WHERE
clause.

Sorry for messing everyone around!

Jamie

All Unsolicited email is deleted.

First I say you ned a key to join on then I don't even use it!

This may work better:

DELETE *
FROM [Table A] AS T1 INNER JOIN [Table B] AS T2 ON T1.ID = T2.ID
WHERE [Table A].ID = [Table B].ID


Jamie

Folks, don't bother with the "clever" virus infected emails you are
sending. All Unsolicited email is destroyed.

I have no idea what those values represent, but I"ll have a crack at
answering your question anyway.

You must have a unique key (ID) in each table which can be joined,
enabling an exact match of rows. If you have, you could try:

DELETE *
FROM [Table A]
WHERE [Table A].ID = [Table B].ID

You didn't say what RDMS you're using, but the above should work in
Access. If you're using SQL server you can omit the " * " from the
statement.


Jamie

I have the following 2 tables
Table A
01 02 03 04 05 06 07
08 09 10 11 12 13 14
15 16 17 18 19 20 21
30 32 34 39 45 47 50
80 81 85 87 89 90 91

Table A has 5 records.

Table B
01 02 03 04 05 06 07
15 16 17 18 19 20 21
51 53 55 56 58 59 60
31 33 35 36 38 70 71
80 81 85 87 89 90 91

New records are not added in Table A, they are added in
Table B.
Table A and Table B's 3 records are the same. I want
records of Table A that are same in Table B to be deleted
from Table A.
When deleted the total number of records in Table A
should not increase but decrease, for eg in the above
tables 3 records are the same so when deleted Table A
should have only 2 records left.

I would really appreciate for your help
Thank you