You will have to use some criteria other than table position to delete just
the "first" match in table 1. The concept of "first" and "last" only apply
to ordered sets of data and there is _no_ guarantee that records entered
sequentially will be _stored_ sequentially in the actual data structure.
Are there other columns that make the "second" #1 in your example worthy of
keeping? What makes that record "special" enough to survive when the other
matching row won't?
Sorry I couldn't help,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
"[EMAIL PROTECTED]"
<rinkeh To: [EMAIL PROTECTED]
cc:
06/23/2004 08:24 Fax to:
AM Subject: one on one joins
Please respond to
rinkeh
Hi list,
I have a problem concerning two tables. Basically, I need a strict
"one on one join".
Simplyfied, the problem is as follows:
I need a check on two tables:
T1: containing four records, with the value of field id being 1, 2, 3, 4
T2, same structure, containing the records 1, 2, 3, 5 for field id.
I want to delete anything in table T1 which is not in T2. That is
simple:
delete t1 from t1 a left join t2 b
using(id)
where
b.id is not null;
However, Now my problem:
the problem is, that the id in both tables can contain duplicates.
When the tables have the following recs:
T1: field id: 1,1,2,3,4
T2: field id: 1,2,3,5
I want the delete command to delete ONE 1, the 2 and the 3, thus
resulting in a table T1 having left only two recrods, with id 1 and 4.
However, the delete matches both 1-records of T1 to the single 1-
record of T2, so both of them are deleted.
So, summarizing: I need a sort of "one on one join", which joins
only one single 1 in table T1 to a single 1 in table T2, and when
there's no 1 record left in T2, the other 1 in T1 should be
unmatched.
How can I do this?
Or if this is not possible, does somebody know some smart
workaround trick?
Thanks, rinke
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
