update mysql field using CONCAT function?

Question

i need a simple query which update mysql field with its value and some modification for example if field have value (3,4,5) i can add (,6) and this just in case it have value and if its NULL then update the field with the ","

i searched web and find mysql function called CONCAT and it Returns the string that results from concatenating the arguments.

i tried this one but this didn't work

UPDATE groups SET SubsID = CONCAT(SubsID,",5") WHERE GroupID = 2

it return no issue but 0 rows affected and field not updated

Barmar · Accepted Answer · 2012-10-15 06:26:42Z

2

UPDATE GROUPS
SET SubsID = CASE WHEN SubsID IS NULL THEN '6'
                  ELSE CONCAT(SubsID, ',6')
             END
WHERE GroupID = 2

answered Oct 15, 2012 at 6:26

Barmar

789k57 gold badges555 silver badges669 bronze badges

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

Comments

Michel Ayres · Accepted Answer · 2014-03-13 12:01:57Z

1

Add COALESCE before concatenating

UPDATE groups 
SET SubsID = CONCAT(COALESCE(SubsID,''),",5") 
WHERE GroupID = 2

edited Mar 13, 2014 at 12:01

Michel Ayres

6,02511 gold badges67 silver badges97 bronze badges

answered Oct 15, 2012 at 5:52

John Woo

265k70 gold badges509 silver badges500 bronze badges

1 Comment

Dr.Neo Over a year ago

great but when the filed is null it still add the comma i want add the comma only in case the filed is not null otherwise i want add the number only without comma

Phil · Accepted Answer · 2012-10-15 06:02:12Z

0

CSV data in a relational database is a terrible idea. Instead, you should use a junction table to store many-to-many relationships between groups and subs.

For example

CREATE TABLE group_to_sub (
    `GroupID` INT NOT NULL REFERENCES `groups` (`GroupID`),
    `SubID` INT NOT NULL REFERENCES `subs` (`SubID`),
    PRIMARY KEY (`GroupID`, `SubID`)
) ENGINE innodb;

INSERT INTO group_to_sub (GroupID, SubID) VALUES (2, 5);

edited Oct 15, 2012 at 6:02

answered Oct 15, 2012 at 5:55

Phil

166k25 gold badges265 silver badges269 bronze badges

Comments

Himanshu · Accepted Answer · 2012-10-15 06:27:02Z

-2

The query is wrong..Try the below one

UPDATE groups SET SubsID = CONCAT(SubsID,',5') WHERE GroupID = 2;

And make sure that groupid 2 exists

edited Oct 15, 2012 at 6:27

Himanshu

32.8k32 gold badges117 silver badges137 bronze badges

answered Oct 15, 2012 at 5:57

Hemanth Bodapati

71 bronze badge

1 Comment

Phil Over a year ago

All you've changed is the quotes which don't make a difference in this case

Collectives™ on Stack Overflow

update mysql field using CONCAT function?

4 Answers 4

Comments

1 Comment

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related