SQL Update All Values in a Field with Appended String Concat Not Working

SQL UPDATE all values in a field with appended string CONCAT not working

That's pretty much all you need:

mysql> select * from t;
+------+-------+
| id   | data  |
+------+-------+
|    1 | max   |
|    2 | linda |
|    3 | sam   |
|    4 | henry |
+------+-------+
4 rows in set (0.02 sec)

mysql> update t set data=concat(data, 'a');
Query OK, 4 rows affected (0.01 sec)
Rows matched: 4  Changed: 4  Warnings: 0

mysql> select * from t;
+------+--------+
| id   | data   |
+------+--------+
|    1 | maxa   |
|    2 | lindaa |
|    3 | sama   |
|    4 | henrya |
+------+--------+
4 rows in set (0.00 sec)

Not sure why you'd be having trouble, though I am testing this on 5.1.41

MySQL Update concat field value only if it does not contain a string

You can use like and concat():

update tbl
    set descr = concat(descr, ' ', brand)
    where descr not like concat('%', brand, '%');

This adds a space before the brand for readability.

Update mysql field using CONCAT and SELECT

Do you need the sub query?

UPDATE tbl 
SET string_id = CONCAT(string_id, ',13,14,15,16')
WHERE id = @id;

Note that in MySQL you cannot modify using an UPDATE the table that is used in the sub query (although there are fiddles around it):-

https://dev.mysql.com/doc/refman/5.5/en/subqueries.html

In MySQL, you cannot modify a table and select from the same table in a subquery. This applies to statements such as DELETE, INSERT,
REPLACE, UPDATE, and (because subqueries can be used in the SET
clause) LOAD DATA INFILE.

UPDATE with multiple values and CONCAT

This is a well-know problem when an UPDATE ... JOIN ... has multiple matching rows; typically, only one update happens.

You can work around this by pre-aggregating the product names of each spare in a subquery using group_concat(), and then join.

UPDATE products_description pd
INNER JOIN (
    SELECT spare_id, GROUP_CONCAT(products_name separator ' ') products_names
    FROM products_spare 
    GROUP BY spare_id
) ps ON pd.products_id = ps.spare_id
SET pd.products_keywords = CONCAT(pd.products_keywords, ' ', ps.products_names)

Side notes:

• you probably want an INNER JOIN instead of a LEFT JOIN (when there is no matching record, there is nothing to update)

• use single quotes for strings instead of double quotes: this is the SQL standard

How can I append a string to an existing field in MySQL?

You need to use the CONCAT() function in MySQL for string concatenation:

UPDATE categories SET code = CONCAT(code, '_standard') WHERE id = 1;

MySQL update query with 'concat()' value based on a 'CASE' not working

If I understand correctly, you should move the condition to the WHERE clause:

update my_table
    set my_column = concat(my_column, $additional_condition)
    where my_name in ('x', 'y') and
          my_column not like '%$additional_condition%';

How to concat all values associated with a key?

I think user is a reserved word, so you will have to resolve that. Otherwise, I think something like this will work for you:

select user, string_agg (phonetype, '' order by phonetype)
from table1
group by user

-- EDIT 4/21/2022 --

Aah, okay. I did not glean that from the original question.

What if you used the distinct on the original table before the aggregation?

select userid, string_agg (phonetype, '' order by phonetype)
from (select distinct userid, phonetype, uniquephoneid from table1) x
group by userid

I got these results from this version:

1   AABC
2   ACDE
3   ABBB

If that logic still doesn't work, can you alter the sample data to find an example where it fails?

How to prepend a string to a column value in MySQL?

You can use the CONCAT function to do that:

UPDATE tbl SET col=CONCAT('test',col);

If you want to get cleverer and only update columns which don't already have test prepended, try

UPDATE tbl SET col=CONCAT('test',col)
WHERE col NOT LIKE 'test%';

---

Related Topics