3

I apologize for the silliness of the question but am a complete neophyte with MySQL and am having trouble even reading the documentation for this. I have a table with two columns "homeid" and "indid", which are both integer data fields. I want to concatenate these together in a new variable "uid" with a hyphen. Desired output would look like this: 

uid   homeid   indid
10-1  10       1
10-2  10       2
11-1  11       1

I have tried the following code, which does fine through generating the variable "uid" in which the concatenated variables should sit, but doesn't seem to work (though produces no error) beyond that: 

ALTER TABLE table_name
add column uid varchar(10) FIRST; /*adds column uid to table "table_name" as first column*/

UPDATE table_name
SET uid=CONCAT('-' , homeid, indid);

Thanks in advance for your help (and patience).

SMM
  • 193
  • 1
  • 3
  • 12

2 Answers2

10

The function you need here is CONCAT_WS() to use a delimiting separator -, or change around the arguments to CONCAT():

UPDATE table_name SET uid = CONCAT_WS('-' , homeid, indid);

OR change around the arguments to CONCAT()

UPDATE table_name SET uid = CONCAT(homeid, '-', indid);
Michael Berkowski
  • 267,341
  • 46
  • 444
  • 390
  • Thanks for your help. I tried with both CONCAT_WS and CONCAT and neither works (again with no error). Is it possible that it is because 'uid' is initially populated with NULL values? If so, is there an easy workaround to that? Thanks. – SMM Jan 24 '12 at 04:27
  • The problem seems to have to do with the SET portion of this as a SELECT query returns the correct values. – SMM Jan 24 '12 at 06:43
  • GAH! I am an ass. I just needed to reload the darn table! – SMM Jan 24 '12 at 07:28
-1

please try following query. 

UPDATE table_name SET uid=CONCAT('-' , homeid, indid) where homeid > 0;
Lucifer
  • 29,392
  • 25
  • 90
  • 143