[SOLVED] update a table column data

[shamsuljewel]

I hv a table name table1 and column name col1 which contain data jewel,alam,etc

so if i want to change jewel to shah that is col1 data will be shah,alam,etc how do I do it?

what is the query?

[rajivgonsalves]

well try this

 

update table1 set col1='shah' where col1='jewel'

[shamsuljewel]

no col1 doesnot contain only jewel but jewel,alam,etc

so this query doesnot match or find the jewel at col1

[fenway]

no col1 doesnot contain only jewel but jewel,alam,etc

so this query doesnot match or find the jewel at col1

Do you want to change each of those values to a new value, or conditionally change each one?

[rajivgonsalves]

here you go pretty heavy but if you put in data that way you have to run some heavy queries to update it

 

update table1 set col1 = REPLACE(REPLACE(REPLACE(ip,'jewel,','shah,'),',jewel',',shah'),',jewel,',',shah,') where col1 like 'jewel,%' or col1 like '%,jewel,%' or col1 like '%,jewel'

[fenway]

Oh, it's comma-separated? Terrible...

[shamsuljewel]

I want to change each value conditionally.

[rajivgonsalves]

what do you mean conditionally ?

[shamsuljewel]

that is I put some user_id those who are my friends in the column. So if I delete a user_id from the list then I just want to delete the specific user_id all the other remains same.

[rajivgonsalves]

this should work for a delete

 

update table1 set col1 = REPLACE(REPLACE(REPLACE(ip,'jewel,',''),',jewel',''),',jewel,',',') where col1 like 'jewel,%' or col1 like '%,jewel,%' or col1 like '%,jewel'

 

this will delete all jewel enteries

[shamsuljewel]

so If I want to add a new user_id to the column? can u please show me the query?

[rajivgonsalves]

you can try the following

 

update table1 set col1=(CASE WHEN col1 is null THEN 'jewel' ELSE concat(col1,',','jewel') END) 

[shamsuljewel]

thanks for ur help....U solved my problems..thanks rajivgonsalves