MYSQL更新查询以删除空格 [英] MYSQL update query to remove spaces

问题描述

我的一位客户在我们的一个应用程序中添加了多个帐号. 尝试进行交易时，由于帐号末尾的空格，交易失败. 我如何更新他在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);

这篇关于MYSQL更新查询以删除空格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！