Re: Using a case within a simple update
From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Thu, 15 Oct 2009 17:28:34 +0200
Message-ID: <4AD73FA2.1090603_at_gmail.com>
Sashi wrote:
> Hi all, I'm trying to do something like this.
>
> update myTable
> case length(duration)
> when 8 then set hours = substr(duration,1,2)
> else set hours = substr(duration,1, 1)
> end case
>
> Depending on the length of the 'duration' field, I'm trying to extract
> either the first or the first two characters.
> It ain't workin'.
>
> Is such a construct possible withing pl/sql? The few examples that
> I've googled around give easy options for using it within a select but
> no examples within an update.
>
> Thanks,
> Sashi
Date: Thu, 15 Oct 2009 17:28:34 +0200
Message-ID: <4AD73FA2.1090603_at_gmail.com>
Sashi wrote:
> Hi all, I'm trying to do something like this.
>
> update myTable
> case length(duration)
> when 8 then set hours = substr(duration,1,2)
> else set hours = substr(duration,1, 1)
> end case
>
> Depending on the length of the 'duration' field, I'm trying to extract
> either the first or the first two characters.
> It ain't workin'.
>
> Is such a construct possible withing pl/sql? The few examples that
> I've googled around give easy options for using it within a select but
> no examples within an update.
>
> Thanks,
> Sashi
update mytable set hours=case when length(duration)=8 then substr(duration,1,2) else substr(duration,1,1) end
or shorter
update mytable set
hours=decode(length(duration),8,substr(duration,1,2),substr(duration,1,1))
(assuming you wish update all rows in your table) Best regards
Maxim Received on Thu Oct 15 2009 - 10:28:34 CDT