In sort of pseudocode I want:

select ID, A from TableI
where field_B = 'a value'
and (if A not in ('value 1', 'value 2', ....)
then set A to '')

Would something like this work?

select A from TableI
where field_B = 'a value'
and A IN ('value 1', 'value 2', ....)
union all
select ID, '' as A from TableI
where field B = 'a value'
and A NOT IN ('value 1', 'value 2', ....)

On 24-08-2011 20:08, anonymous wrote:
no, because you want to set A to '' you will need an update:

UPDATE TableI
SET A=''
WHERE field_B='a value'
AND A NOT in ('value 1', 'value 2')



--
Luuk

On 8/24/2011 2:23 PM, Luuk wrote:
Sorry, I wasn't clear. I don't want to set A in the table to ''. That
is unchanged. What I want is for the select statement to return the
table's value of A if it is in the provided list, otherwise to return a
'' -- and both with the associated ID.

I will then be showing a list of IDs along with the associated values of
A. The problem is that some of those values of A are invalid, so I want
to force the user to pick a valid one from the list that I would
provide. I can do that by blanking out the value derived from the table
if it is invalid.

--
Shelly

On 8/24/2011 2:23 PM, Luuk wrote:
is unchanged. What I want is for the select statement to return the
table's value of A if it is in the provided list, otherwise to return a
'' -- and both with the associated ID.

I will then be showing a list of IDs along with the associated values of
A. The problem is that some of those values of A are invalid, so I want
to force the user to pick a valid one from the list that I would
provide. I can do that by blanking out the value derived from the table
if it is invalid.

On 24-08-2011 20:50, anonymous wrote:
If you do a UNION, both parts should return the same number of fields.

select ID, A from TableI
where field_B = 'a value'
and A IN ('value 1', 'value 2', ....)
union all
select ID, '' as A from TableI
where field B = 'a value'
and A NOT IN ('value 1', 'value 2', ....)

but this can be done also like this:

SELECT
ID, IF (A IN ('value 1', 'value 2'),A,'') as A
FROM TableI
where field_B = 'a value'


--
Luuk

On 8/24/2011 4:05 PM, Luuk wrote:
My example does that. Not the "'' as A"

Nice. Thanks. That is the syntax I was looking for.

El 24/08/2011 22:05, Luuk escribió/wrote:
Another possibility:

SELECT ID,
CASE
WHEN A NOT IN ('value 1', 'value 2')
THEN ''
ELSE A
END AS A
FROM TableI
WHERE field_B = 'a value'

In this example it's equivalent. The main difference is that CASE...END
can be generalised to N conditions (and it's also portable to other DBMS).


--
-- http://alvaro.es - Álvaro G. Vicario - Burgos, Spain
-- Mi sitio sobre programación web: http://borrame.com
-- Mi web de humor satinado: http://www.demogracia.com
--

On 8/25/2011 2:25 AM, "Álvaro G. Vicario" wrote:
Thanks. That is actually what I wound up with after further investigation.