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
TABLE:
RECNO | ID | TYPE
---------------------------------
1 | 1 | NULL
2 | 2 | 4 <-KEEP AS IS
3 | 2 | 6 <- DROP
4 | 3 | NULL <- KEEP AS IS
5 | 4 | NULL <-KEEP AS IS
6 | 5 | 1 <-NULL
7 | 5 | 3 <-DROP
8 | 6 | 7 <-NULL
9 | 6 | 3 <-DROP
What I neede is a SQL statement that for a given value of TYPE, lets say
4,
selects all the unique id's with TYPE = 4 when appropriate otherwise NULL
like:
SOLUTION:
RECNO | ID | TYPE
---------------------------------
1 | 1 | NULL
2 | 2 | 4
3 | 3 | NULL
4 | 4 | NULL
5 | 5 | NULL
6 | 6 | NULL
I know this isn't MySQL specifik but hope for someones help anyway :-)
Sorry for any inconvenience this may course users of the list
I've reread your question and example a couple of times but I'm still not
clear on what you want but maybe this will help until you can clarify the
question.
You use WHERE to limit your result set to only specific rows of the
original table. Therefore, if you want only rows where the type is 4, you
say:
Select <column-list>
from <table-name>
where type = 4;
For example, if your table name was 'mytab' and you wanted the ID column
in the result, you'd write:
Select ID
from mytab
where type = 4;
Now, if the result showed many identical values in the columns of the
result set and you just want to know the unique values of ID that had a
type of 4, you add DISTINCT to the query:
select distinct ID
from mytab
where type = 4;
But that's where I get confused by your description of your problem. There
is only one row in your sample table that has a type of 4 so DISTINCT
isn't going to do anything for you; you'll get the same result with or
without DISTINCT, at least with the data you've shown.
I also don't understand where the nulls come in. Do you actually want to
update the data in your table permanently so that a null isn't null any
longer (or a non-null value is null)? Or do you want to _display_ a null
where something isn't null? I don't understand what your "DROP" and "LEAVE
AS IS" remarks mean.
Can you explain more fully what you are trying to do?
--
Rhino
--
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]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
