I think he wants to update rows where != 4 to null
that is, update TYPE setting TYPE to null where TYPE != 4
-Ron
Rhino wrote:
----- Original Message ----- From: "Søren Merser" <[EMAIL PROTECTED]>
To: <mysql@lists.mysql.com>
Sent: Sunday, March 05, 2006 9:12 AM
Subject: Help on sql statement (not MySQL specifik)
Hi
Could someone please help me out here?
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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
