I have a table named comments_live which has 4 fields and some 24000 entries. I want to add another field which is the output of a select query. The query is like this:

INSERT INTO comments_live SELECT SUBSTR(record,1, POSITION(' ' IN record)) AS project_id FROM comments_live;

What this query is doing is it's appending the result of the SELECT query to the table. I want the 'project_id' field to be appended to the existing 24000 rows. i.e. The above query should not add those extra rows to the table.

Any help would be appreciated.

PS: I tried adding a empty column 'project_id' to the table first and then executing the query, still I'm getting the same result.

CodePudding user response：

I conclude from your code example that you want to add a column whose content is the substring from the column RECORD. If you succeeded in adding the empty column, then you just need code like the following:

update comments_live
set project_id=substr(record,1,[...])