I have a table - Sample

I want to split the values of Column_B using the delimiter '-', place the second part in Column_A.

I have written below query which gives me syntax error ( Error occurs at this 'from': from (SELECT Column_A.....)

Update Sample a SET Column_A = New_Sample_Table.New_Column from (SELECT Column_A , Column_B, SUBSTR(Column_B, instr(Column_B, '-') 1) AS New_Column from Sample b) New_Sample_Table WHERE a.Column_B = New_Sample_Table.Column_B;

How can I fix this ?

CodePudding user response：

If your description is correct and you didn't miss to tell us anything important, you don't need a subquery for that. You can do something like this:

UPDATE yourtable SET column_a = 
CONCAT(column_a,'-',SUBSTRING_INDEX(column_b,'-',-1)) 
WHERE column_b LIKE '%-%';

SUBSTRING_INDEX will find the substring right from the "-" in the second column. CONCAT will put the two strings and the "-" together. The WHERE clause prevents that the first column will be updated if the second column doesn't contain a "-".

If it's not intended to put the "-" to the first column, but only the substring after that, just remove this from the CONCAT part:

UPDATE yourtable
SET column_a = CONCAT(column_a,SUBSTRING_INDEX(column_b,'-',-1))
WHERE column_b LIKE '%-%';

See here a working example according to your sample data: db<>fiddle