Thx Martijn!

I actually found another way...

col1 = case when :inVal1 is null then col1 else : inVal1 end,

Using COALESCE makes a lot less typing, so I'll use that next time :)

Henrik

-----Original Message-----
From: Martijn Tonies [mailto:m.tonies@...]
Sent: 6. februar 2004 21:50
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] sql update

Hi Henrik,

> Hi, in a stored procedure I say something like this:
>
> update aTable
> set col1 = :inVal1, col2 = :inVal2, col3 = :inVal3
> where col4 = '...';
>
> inVal1, inVal2, inVal3 and inVal4 are input parameters, and the thing

is

> that I just want to update the columns where the input parameters are
> not null. So if inVal1 is null and the other input parameters are not,

I

> want to update col2 and col3 but not col1.
>
> Is there a clever way to do this, using some sort of if/case...

Firebird 1.5? Cause then you might want to try something like:

SET COL1 = COALESCE(:inVal1, COL1,
COL2 = ...

With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS
SQL
Server.
Upscene Productions
http://www.upscene.com





Yahoo! Groups Links