MYSQL 更新查询以删除空格

2022-01-17 00:00:00 sql-update mysql

我的一位客户在我们的一个应用程序中添加了多个帐号.在尝试进行交易时，由于帐号末尾的空格，交易失败.我如何更新他在 Mysql 数据库中的记录以从最后拥有它们的帐户中删除所有空格，而不让他删除客户并重新添加帐户?表结构如下:

One of my clients has added a number of account numbers in one of our applications. While trying to make a transaction the transaction fails due to the spaces at the end of the account number. How do i update his records in the Mysql database to remove all the spaces from accounts that have them at the end, without making him delete the clients and re-adding the accounts? the structure of the table(s) is as follows:

不知道如何构造查询或mysql的功能

Not sure how to structure the query or the function of the mysql

账户表:

the account table: CUSTOMER_ID ACCOUNTNUMBER TXT CURRENCY_NO USER_ID ACTIVE_FLAG USER_DATE ben_bic_address int_bic_address the admin table ADM_USER_ID LOCATION_CD LANG USER_NAME USER_LOGIN USER_PASSWORD GROUP_CODE USER_ID USER_DATE ACTIVE COUNTER connected IP And the customer table: CUSTOMER_ID COUNTRY_NO USER_ID CUSTOMER_NAME ACTIVE_FLAG

推荐答案

如果你需要RTRIM()特定客户的所有账户，你可以使用JOIN 你的 UPDATE 语句如下:

If you need to RTRIM() all the accounts of a particular customer, you can use a JOIN with your UPDATE statement as follows:

UPDATE accounts_table INNER JOIN customers_table ON (accounts_table.user_id = customers_table.user_id) SET accountnumber = RTRIM(accountnumber) WHERE customers_table.customer_id = 'customer id';

如果您的accounts_table 中的记录不多，并且您想确保所有accountnumber 值都被修剪，您可以简单地将修剪应用于所有记录，如下所示:

If you do not have many records in accounts_table, and you want to make sure that all the accountnumber values are trimmed, you can simply apply the trim to all the records as follows:

UPDATE accounts_table SET accountnumber = TRIM(accountnumber);

相关文章