Problem with UPDATE and IF

28 Jul 2006 1:36 PM

mario.kornmesser

Hello,

I have problems to arrange a proper SQL-statement. We are using
SQL-Server 2000 and Win 2003 Server
It seems that the failure is located in the "WHERE-clause". Especially
the IF-statement produces some errors (Runtime)
Maybe someone can check the syntax for a SQL-newbie.
I've tried to open the IF-statement because of the fact that "NOT LIKE"
doesn't work on columns with the value 'NULL'.

UPDATE project SET reference_list_year_all=('2010 ' +
isNULL(reference_list_year_all,'')) WHERE [project number]='4294' AND
IF(reference_list_year_all IS NOT NULL) THEN reference_list_year_all
NOT LIKE '%2010%' END ;

Thanks a lot.

Mario

28 Jul 2006 2:15 PM

Mike C#

Get rid of the IF and get rid of the parens around ISNULL. If doesn't
belong in a WHERE clause. I changed your ISNULL to COALESCE. Try this:

UPDATE project
SET reference_list_year_all = '2010 ' +
    COALESCE(reference_list_year_all,'')
WHERE [project number] = '4294'
    AND (reference_list_year_all IS NOT NULL
    AND reference_list_year_all
        NOT LIKE '%2010%');

<mario.kornmes***@gmx.de> wrote in message
Show quote

news:1154093787.801534.199460@s13g2000cwa.googlegroups.com...
>
> Hello,
>
> I have problems to arrange a proper SQL-statement. We are using
> SQL-Server 2000 and Win 2003 Server
> It seems that the failure is located in the "WHERE-clause". Especially
> the IF-statement produces some errors (Runtime)
> Maybe someone can check the syntax for a SQL-newbie.
> I've tried to open the IF-statement because of the fact that "NOT LIKE"
> doesn't work on columns with the value 'NULL'.
>
> UPDATE project SET reference_list_year_all=('2010 ' +
> isNULL(reference_list_year_all,'')) WHERE [project number]='4294' AND
> IF(reference_list_year_all IS NOT NULL) THEN reference_list_year_all
> NOT LIKE '%2010%' END ;
>
>
> Thanks a lot.
>
> Mario
>