Basic SQL question Update statement

[bruehlicke]

I have a trigger something like

..... UPDATE X SET A=(SELECT ..... )


Now, if the embedded SELECT statement returns no rows I would NOT like
to change the value from A to "bank/null" but let it keep the value it
currently has.

Any suggestions for a one liner which fits into a create trigger statement

B-)

[bruehlicke]

I cannot set the Column to NO NULL,

Maybe a hack - but just added another trigger which will react on any
NULL's inserted and convert them to the default value.

B-)

On Fri, Sep 25, 2009 at 3:08 PM, bruehlicke <bruehlicke@...> wrote:

> I have a trigger something like
>
> ..... UPDATE X SET A=(SELECT ..... )
>
>
> Now, if the embedded SELECT statement returns no rows I would NOT like
> to change the value from A to "bank/null" but let it keep the value it
> currently has.
>
> Any suggestions for a one liner which fits into a create trigger statement
>
> B-)
>

[Knut Anders Hatlen]

bruehlicke <bruehlicke@...> writes:

> I have a trigger something like
>
> ..... UPDATE X SET A=(SELECT ..... )
>
>
> Now, if the embedded SELECT statement returns no rows I would NOT like
> to change the value from A to "bank/null" but let it keep the value it
> currently has.
>
> Any suggestions for a one liner which fits into a create trigger statement

I think this should do the trick:

UPDATE X SET A=COALESCE((SELECT ......), A)

--
Knut Anders

[bruehlicke]

Tusind takk. Virker perfekt.

On Sun, Sep 27, 2009 at 1:22 PM, Knut Anders Hatlen <Knut.Hatlen@...> wrote:

> bruehlicke <bruehlicke@...> writes:
>
>> I have a trigger something like
>>
>> ..... UPDATE X SET A=(SELECT ..... )
>>
>>
>> Now, if the embedded SELECT statement returns no rows I would NOT like
>> to change the value from A to "bank/null" but let it keep the value it
>> currently has.
>>
>> Any suggestions for a one liner which fits into a create trigger statement
>
> I think this should do the trick:
>
> UPDATE X SET A=COALESCE((SELECT ......), A)
>
> --
> Knut Anders
>