Menu
Here is what I want to do:
current table:
+----+-------------+ | id | data | +----+-------------+ | 1 | max | | 2 | linda | | 3 | sam | | 4 | henry | +----+-------------+ Mystery Query ( something like "UPDATE table SET data = CONCAT(data, 'a')" )
resulting table:
+----+-------------+ | id | data | +----+-------------+ | 1 | maxa | | 2 | lindaa | | 3 | sama | | 4 | henrya | +----+-------------+ thats it! I just need to do it in a single query, but can't seem to find a way. I am using mySQL on bluehost (I think its version 4.1)
Thanks everyone.
508
Here each user posts are different so we can't apply any update command so we will use concat to add the site signature to each post kept inside a record field in our update command. Now let us see how it is used in a MySQL table query.
To prepend a string to a column value in MySQL, we can use the function CONCAT. The CONCAT function can be used with UPDATE statement. Creating a table.
First, use CONCAT to get the customers' full name. Then use CONCAT and SUBSTR together to retrieve the email, use REPEAT and INSTR to censor it and use AS to rename the column. With INSTR we will identify a string ( email ) and specify a certain character ( @ )as the starting position we want in the string.
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
110
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
