Create a MySQL table cyclically and update the two tables.

Create a MySQL table cyclically and update the two tables.

Create tables cyclically Using Stored Procedures

DELIMITER //

Create procedure test. create_channel_avail ()

BEGIN

DECLARE '@ I' INT (11 );

DECLARE '@ sqlstr' VARCHAR (2800 );

SET '@ I' = 1;

WHILE '@ I' <101 DO

SET @ sqlstr = CONCAT (

"Create table channel_avail ",

'@ I ',"(

'Prop' VARCHAR (40 ),

'Stay _ date' date,

'Roomcode' VARCHAR (40 ),

'Channel _ Code' VARCHAR (40 ),

'Status' VARCHAR (1) DEFAULT 'A ',

'Createtime' DATETIME comment' create timestamp ',

'Updatetime 'datetime comment' update timestamp ',

Primary key ('prop', 'stay _ date', 'roomcode', 'Channel _ Code ')

) ENGINE = INNODB

Default charset = utf8;

"

);

PREPARE stmt FROM @ sqlstr;

EXECUTE stmt;

SET '@ I' = '@ I' + 1;

End while;

END;

CALL test. create_channel_avail_db ();

Drop procedure test. create_channel_avail_db;

Note the following when using MySQL database, update, and select to update table data:

For example, table test1
A B
1
2 B
3 c

Table test2
A B
2 d
3 e
4 f
A simple combination of update and select is intended to change columns B of Rows a = 2 and a = 2 in Table test1 to columns d and e.
Update from test1 set B = (select B from a where test1.a = test2.a)
Note that this syntax has a trap. In this case, the values of a = 2 and a = 3 in the updated table test1 are indeed modified to d and e, however, because the data in Row a = 1 is not in the result set, all data is set as null, that is, except that some data in the result set is updated, no value is set to null (columns that are not allowed to be null are not tested yet)

The inner join method is the result we want, so we should replace the preceding SQL statement with this method.

Update test1 inner join test2 ON a. id = B. id

SET test1. B = test2. B

This article permanently updates the link address: