Søren Merser wrote:
Hi, I'll try
I need one record for each id in the tabel, i.e. NO duplicate id's with
TYPE set to 4 or NULL
Now, the TYPE of id 2 is 4 so I peserve it;
As id 2 has more than one entry I have to delete it/them
Id's with TYPE = NULL (id 1,4,5)is kept
Id 5 (and 6) has two records, none of which has the value of 4, so one
is preserved and TYPE set to NULL while the other should be deleted
I update the tabel between queries from another table
Regards Soren
You appear to have several, separate issues. First, your table has duplicate
entries, which you don't want. If you do not want duplicate entries, you should
not allow them. That is, you need a UNIQUE constraint on the id column. You
won't be able to add one, however, until you remove the existing duplicates.
To remove duplicates, you first have to decide which to keep and which to toss.
In your example, you always keep the row with the lowest recno, but your
description implies that when one of the duplicates has type = 4, you want to
keep that one, regardless of recno. Assuming that to be true, you need
something like:
DELETE t1
FROM yourtable t1 JOIN yourtable t2 ON t1.id = t2.id
WHERE (t1.recno > t2.recno AND t1.type !=4)
OR (t1.recno < t2.recno AND t2.type = 4);
(The exact syntax depends on your version of mysql. See the manual for details
<http://dev.mysql.com/doc/refman/4.1/en/delete.html>.)
Now add a UNIQUE constraint on id so this won't ever happen again:
ALTER TABLE yourtable ADD UNIQUE (id);
In your example, you have renumbered recno. This is almost always a bad idea,
but you can do it with:
SET @i = 0;
UPDATE yourtable SET recno = (@i := @i + 1);
Finally, now that you've fixed the table, the requested update is simple:
UPDATE yourtable SET type = NULL WHERE type != 4;
Having said all that, I'm skeptical this is the best solution. Perhaps it is
just lack of imagination on my part, but I'm having trouble seeing why you would
want to do things this way. Having a column which should be unique, but isn't,
and wanting to renumber your primary key column are both red flags. I also find
it strange that you seem to want to find the unique ids with type = 4, but you
are changing every other type to NULL in the process. Why not just select what
you want, as Rhino suggested?
SELECT DISTINCT id FROM yourtable WHERE type = 4;
Perhaps this all makes sense given the context. (You haven't told us much). On
the other hand, if you carefully describe what you are trying to accomplish, one
of the many experts on the list may well be able to supply you with a better way.
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
