how can update and delete in a row using mysql

Question

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

Amrinder Singh · Accepted Answer · 2015-10-27 11:57:41Z

1

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)

answered Oct 27, 2015 at 11:57

Amrinder Singh

5,62213 gold badges50 silver badges95 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 11:52:19Z

0

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

edited May 23, 2017 at 11:52

CommunityBot

11 silver badge

answered Oct 27, 2015 at 12:37

Samrat Aher

765 bronze badges

Collectives™ on Stack Overflow

how can update and delete in a row using mysql

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related