The following example is for showcasing the problem. On a meta level I need to take the value from column A, calculate something and insert the result into column B. The rows in column A contain a lot of duplicates. So maybe this can be used to make the statement faster.

I have a table, say 'fruitamount', where I added a new column, say 'newname'. It now looks like this:

name        amount        newname
-----------------------------------------
'  apple'   2
' melon '   5
'  apple'   1
' melon '   4

The strings in column 'name' do have unnecessary whitespaces and the rows in column 'newname' are empty.

Now I want to populate the rows in column 'newname' with the trimmed versions of the strings in column 'name'. I am able to create a result with two columns, which contain (a) the distinct values of column 'name' and (b) their trimmed versions by using this query ...

SELECT DISTINCT
    name AS oldname,
    TRIM(name) AS newname
FROM fruitamount;

The result looks like this:

oldname        newname
--------------------------
'  apple'      'apple'
' melon '      'melon'

.. but I would need to use this result in an outer update to the table 'fruitamount', which inserts the value from 'newname' in the according rows. And I have no clue how to do it, if it is possible at all. If it is not possible by the way of inner/outer statement, is there any other way?

Thanks!

CodePudding user response：

You can create the newcolumn and if you do not have an id autoincrement you can add it ,too.

CREATE TABLE fruitamount(
id INT,
name varchar(10),
amount int);

insert into fruitamount values
(1,'  apple',2),
(2,' melon ',5),
(3,'  apple',1),
(4,' melon ',4);

alter table fruitamount add  column newcolumn varchar(10) ;

update fruitamount set newcolumn=  RTRIM(LTRIM(name)) ;

After update you can drop the column:

alter table fruitamount drop  column name  ;

CodePudding user response：

It's not completely clear what the end result you're looking for is, but here are a few options:

Note: the extra whitespace in the name columns doesn't appear on stackoverflow.com

1. Create a new table. Select the normalized data and insert it into a new (temporary) table. Then drop the old table and rename the new table.

/* show initial table */
SELECT * FROM fruitamount;

/* create a new table with the normalized data */
CREATE TABLE fruitamount_normalized AS
SELECT
    SUM(amount) as amount,
    TRIM(name) AS newname
FROM fruitamount
GROUP BY TRIM(name);

/* delete the old table */
DROP TABLE fruitamount;

/* rename the new table */
RENAME TABLE fruitamount_normalized TO fruitamount;

/* show new table/data */
SELECT * FROM fruitamount;

2. Select summed/trimmed data

SELECT
    SUM(amount) as amount,
    TRIM(name) AS newname
FROM fruitamount
GROUP BY TRIM(name);

3. Update table and then select DISTINCT data. This will cause duplicate rows which will need to be cleaned up separately.

/*
  update the fruitamount table with the new values
*/
UPDATE fruitamount fa
JOIN (
  SELECT
      SUM(amount) as amount,
      TRIM(name) AS newname
  FROM fruitamount
  GROUP BY TRIM(name)
) fa2
SET fa.amount = fa2.amount, fa.newname = fa2.newname WHERE TRIM(name) = fa2.newname;

/* show updated table */
SELECT * FROM fruitamount;

/* only select distinct rows */
SELECT DISTINCT amount, newname FROM fruitamount;