I agree with Michael. If your data is actually messed up, the right solution
is to fix the data and prevent it from getting messed up again FIRST.
Writing SQL that compensates for the messed up data is often possible but
doesn't fix the problem; you'll still need to fix the data and the sooner
you do that, the better.
Fixing the data has a major side-benefit too: it usually makes your queries
a lot easier and more logical. But, as Michael has said, you haven't
explained very much about the 'Big Picture' of your situation so maybe there
is nothing wrong with your data at all. In that case, the query I just
posted should meet your needs.
If you _do_ have a data problem, Michael's suggestions make a lot of sense
but if you don't understand them or if your situation is actually different
than Michael assumes, please post again and people will try to help you.
--
Rhino
----- Original Message -----
From: "Michael Stassen" <[EMAIL PROTECTED]>
To: "Søren Merser" <[EMAIL PROTECTED]>
Cc: <mysql@lists.mysql.com>
Sent: Sunday, March 05, 2006 1:13 PM
Subject: Re: Help on sql statement (not MySQL specifik)
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]
--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.1.2/274 - Release Date: 03/03/2006
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.1.2/274 - Release Date: 03/03/2006
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
