Mysql UPDATE statement assignment nesting with data added after data in table columns

1. Mysql UPDATE statement Assignment nested SELECT

Click ( here ) to collapse or open

1. Update a set col=(Select col from a where id= ' 5 ') where ID>5 and ID<ten;

An error.
ERROR 1093 (HY000): You can ' t specify target table ' a ' for update in FROM clause

After research

The discovery is that the MySQL definition UPDATE statement cannot perform a set assignment on the same sheet at the same time, which means
Update A cannot be followed by select Col from A, which is not a problem if it is a different table operation.

Think of a workaround:

Click ( here ) to collapse or open

1. < Span style= "color: #0000FF;" >update a set col= ( select col from ( select * a ) as b where id= ' 5 ' ) where id> 5 and ID < 10

An alias B will be used to solve this problem by adding select a to the table.


Update mem_player set ' datawarehouse ' = (select ' Datawarehouse ' from (SELECT * from Mem_player) as B where ' Pid ' =100000)
2, Sql-concat (string connection function)

Sometimes, we need to concatenate the data obtained from different fields. Each database provides a way to achieve this:

• Mysql:concat ()
• Oracle:concat (), | |
• SQL Server: +

The syntax for CONCAT () is as follows:

CONCAT (String 1, String 2, String 3,...): String 1, String 2, String 3, and other words concatenated together.

Please note that Oracle's concat () only allows two parameters;

In other words, you can concatenate only two strings at a time. However, in Oracle, we can use the ' | | ' To concatenate multiple strings at once.

Take a look at some examples. Suppose we have the following table:

Geography form

Region_name	Store_name
East	Boston
East	New York
West	Los Angeles
West	San Diego

Example 1:

mysql/oracle:
SELECT CONCAT (region_name,store_name) from Geography
WHERE store_name = ' Boston ';

Results :

' Eastboston '

Example 2:

Oracle:
SELECT Region_name | | "| | Store_name from Geography
WHERE store_name = ' Boston ';

Results :

' East Boston '

Example 3:

SQL Server:
SELECT region_name + "+ store_namefrom Geography
WHERE store_name = ' Boston ';

Results :

' East Boston '

3. Add Separators

Concat_ws () function, representing concat with separator, which is a delimited string connection

Separated by commas, if connected

Mysql> Select Concat_ws (', ', ' 11 ', ' 22 ', ' 33 ');

+-------------------------------+

| Concat_ws (', ', ' 11 ', ' 22 ', ' 33 ') |

+-------------------------------+

| 11,22,33 |

+-------------------------------+

1 row in Set (0.00 sec)

Unlike concat, the CONCAT_WS function does not return NULL because of a null value when it executes

Mysql> Select Concat_ws (', ', ' one ', ' n ', NULL);

+-------------------------------+

| Concat_ws (', ', ' one ', ' + ', NULL) |

+-------------------------------+

| 11,22 |

+-------------------------------+

1 row in Set (0.00 sec)

Here is my use of

  
 

   
  

  
UPDATE   ' Trace_sys '    ' goods_production '   SET   ' image '    =   
   
  

  
(SELECT CONCAT_WS("?",image,"1") FROM (SELECT * FROM goods_production ) AS b WHERE `goodsid` = ‘1‘ ) WHERE `goodsid` = ‘1‘;
  
 

 






From for notes (Wiz)

Mysql UPDATE statement assignment nesting with data added after data in table columns