Postgres Pro
Downloads
Home   >   mailing lists

Thread: update

update

From
"cristi"
Date:
I have a table (my_table) with this structure:
codlm varchar(7)
 
The records are:
45122AD
45122AD
45123AC
440331B
430332C
440222X
I want to change the first 2 letters from 45 to 22 something like this:
 
22122AD
22122AD
22123AC
440331B
430332C
440222X
I tried:
update my_table set codlm='22'||substring(codlm,2) where codlm like '45'
 
but the result is:

 225
 225
 225
 440331B
 430332C
 440222X
Where is the mistake?
 
Thanks!

Re: update

From
Jason Earl
Date:
This looks like it does what you want...

processdata=> select version();
                            version
---------------------------------------------------------------
 PostgreSQL 7.1.3 on i686-pc-linux-gnu, compiled by GCC 2.95.4
(1 row)


processdata=> update my_table set codlm = ('22' || substring(codlm, 3)) where codlm like '45%';
UPDATE 3

processdata=> SELECT * FROM my_table;
  codlm
---------
 440331B
 440332C
 440222X
 22122AD
 22122AD
 22123AC
(6 rows)

Jason

"cristi" <cristi@dmhi.ct.ro> writes:

> I have a table (my_table) with this structure:
>
> codlm varchar(7)
>
>  
>
> The records are:
>
> 45122AD
> 45122AD
> 45123AC
> 440331B
> 430332C
> 440222X
>
> I want to change the first 2 letters from 45 to 22 something like this:
>
>  
>
> 22122AD
> 22122AD
> 22123AC
> 440331B
> 430332C
> 440222X
>
> I tried:
>
> update my_table set codlm='22'||substring(codlm,2) where codlm like '45'
>
>  
>
> but the result is:
>
>
>  225
>  225
>  225
>  440331B
>  430332C
>  440222X
>
> Where is the mistake?
>
>  
>
> Thanks!

Re: update

From
denis@coralindia.com
Date:
TRy
 
update my_table set codlm='22'||substring(codlm,3) where substr(codlm,1,2) = '45'
 
HTH
 
Denis
 
----- Original Message -----
From: cristi
Sent: Tuesday, February 26, 2002 12:13 PM
Subject: [NOVICE] update

I have a table (my_table) with this structure:
codlm varchar(7)
 
The records are:
45122AD
45122AD
45123AC
440331B
430332C
440222X
I want to change the first 2 letters from 45 to 22 something like this:
 
22122AD
22122AD
22123AC
440331B
430332C
440222X
I tried:
update my_table set codlm='22'||substring(codlm,2) where codlm like '45'
 
but the result is:

 225
 225
 225
 440331B
 430332C
 440222X
Where is the mistake?
 
Thanks!

Re: update

From
"cristi69"
Date:

This looks like it does what you want...

processdata=> select version();
                            version
---------------------------------------------------------------
 PostgreSQL 7.1.3 on i686-pc-linux-gnu, compiled by GCC 2.95.4
(1 row)


processdata=> update my_table set codlm = ('22' || substring(codlm, 3))
where codlm like '45%';
UPDATE 3

I think that I need an update for Postgres!
On PostgreSQL 6.5 on i686-pc-linux-gnu, compiled by gcc egs-2.91.66 do not
work!

Thank you very much!