On 2/13/07, jose isaias cabrera <[EMAIL PROTECTED]> wrote:
"P Kishor" wrote,
> On 2/13/07, jose isaias cabrera <[EMAIL PROTECTED]> wrote:
>>
>> Greetings!
>>
>> I would like to be able to update certain columns based on their value.
>> I
>> was looking at "ON CONFLICT", but I could not understand it nor I could
>> not
>> find any examples quickly, so I say, "heck, that's what the list is
>> for..."
>> :-)
>>
>> What I would like is to do something like this,
>>
>> UPDATE table SET
>> ID = '88' if not = '88',
>> parent = '1171291314642' if null,
>> children = '',
>> login = 'blah',
>> notes = 'blah-blah' if null,
>> status = 'o'
>> WHERE ProjID = '88';
>>
>
> Well, if you
> UPDATE table
> SET ID = '88'
> WHERE ProjID = '88'
>
> it will set it to 88 whether or not it is 88. Problem solved. Also
>
> UPDATE table
> SET parent = '1171291314642'
> WHERE ProjID = '88' AND parent IS NULL
>
> will do the appropriate thing only if parent is null. Else, it will
> leave it alone. In any case, WHERE clause is the correct place to put
> your constraints, not the SET clause, unless you write functions that
> return the desired value to be SET.
So, there is on way of doing it in one call/instruction as I previously
exampled, correct? I will have to do them in many calls, correct?
Not that I know of. You want to update different columns (your SET)
based on different criteria (your WHERE)... remember that your
constraint, no matter how complicated it is, acts on the entire set of
values, not on individual elements of that set. Perhaps you could do
nested SELECT queries, but they would be so much more messy and error
prone than multiple calls. You could try something like
UPDATE table SET
ID = '88',
parent = (SELECT CASE WHEN parent IS NULL THEN '1171291314642'
END FROM table WHERE ProjID = '88'),
..
WHERE ProjID = '88';
(My CASE clause syntax is probably incorrect, but you get the idea...
there are also IFNULL and NOTNULL operators). But why? It will be so
convoluted and messy.
Others probably have better ideas.
--
Puneet Kishor http://punkish.eidesis.org/
Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/
Open Source Geospatial Foundation http://www.osgeo.org/education/
---------------------------------------------------------------------
collaborate, communicate, compete
=====================================================================
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
