CODEWITHSUNDEEP
mysql
naresh
naresh

Reputation: 174

how can update and delete in a row using mysql

hello all i have a database where i need to update its one of the fields with a comma separated value. exactly i need to do is something like .. say the value in the field is 872,875,879 and i need to add 812 to this field so that after update it should look like 872,875,879,812 and at the same time i need to update with some empty value 812 in remaining areas in specific_id table.

i dont know how to do this?? the present way to add the number is like

my table :

  sl_no   name   col1   col2   specific_id
   829      x     123    456    859,802
   853      y     111    121    872,875,879
   861      z     101    145    **812**,888,844
   883      a     111    666    877,866

i need output like this

  sl_no   name   col1   col2   specific_id
   829      x     123    456    859,802
   853      y     111    121    872,875,879,**812**
   861      z     101    145    888,844
   883      a     111    666    877,866

i can do like this for update but how can i delete that value in single row of query

update tablename 
set specific_id = Concat(specific_id, ',', '$var') where id = 853

any help please

Upvotes: 1

Views: 56

Answers (2)

Samrat Aher
Samrat Aher

Reputation: 76

You can use update twice. First update query to remove all the records you don't need and then simple update query to update the specific row. For updating(removing) specific value(812) from the whole table use the reference : How to replace a comma separated value in table column with user input value

Upvotes: 0

Amrinder Singh
Amrinder Singh

Reputation: 5502

NO you can't do update and delete in the same query,

you can only combine

DELETE and SELECT
UPDATE and SELECT

This is not a proper way for mysql optimization simply because each query come with different query cost.

And in myisam, it involve table level locking for write

Example for UPDATE and SELECT

UPDATE TABLE_A
  SET SOME_COLUMN = (SELECT SOME_COLUMN_B FROM TABLE_B WHERE ... LIMIT 1)

Example for DELETE and SELECT

DELETE FROM TABLE_A WHERE TABLE_A IN(SELECT ID FROM TABLE_B)

Upvotes: 1

Related Questions