Shulou(Shulou.com)06/01 Report--

Two days ago, the leader suddenly gave me an excel file (the encoding of this file is only a simple code, such as' C001encoding, rather than a sql statement like the one in Table A). There is a code corresponding to rule_code. The leader asked me to replace the Chinese character restriction of the ori_sql column where condition in Table A with the code, and the primary key of Table An is rule_code.

For example:

Select from An a where a.codetype not in (select code from B b where b. Codetypewriter identity type code of the insured policy)

Change to:

Select from An a where a.codetype not in (select code from B b where b.codetypewriter)

Some people think that simply replacing it with replace:

Update A set ori_sql = (select replace (lower (a.ori_sql), 'insurance policy identity type code', 'C001') from A)

At that time, I also tried, but there are tens of millions of data in the A table. Naturally, the where condition is not only a condition of "the identity type code of the insurance policy of the risker". There are more than a thousand results for me to repeat the code that exists in the where condition, which means that I have to run the replace above SQL more than a thousand times, and I will go crazy. Later, I tried to insert the code from excel into table A, the column name is r_code, and the where condition in table An is taken out as a column r_cow, so that thousands of different conditions of a character can be replaced in batches with only one sql statement.

Sql is as follows:

Update An a set a.ori_sql = (select distinct replace (lower (a1.ori_sql), a1.rcircle code) from An A1 where a.ru_code=a1.row_code)

Successfully completed, no need to work overtime!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.