一些SQL操作语句(5),几个修改
返回
TABLE main_type1,DWG'XXXDWG'追加
<![CDATA[
use strict;
use DBI;
my(%t,$n,@fld,@rec,$pref);
# 连接数据库
$$pref{dsn} = "DBI:mysql:host=localhost;database=cookbook";
$$pref{dbh} = DBI->connect($$pref{dsn}, "cbuser", "cbpass") or die "Cannot connect to server\n";
$$pref{dbh}->do("SET NAMES utf8");
if(!$$pref{dbh}){
print "SQL read ERROR!\n";
exit;
}
# 取出main_type1的编号
@{ $t{ptables} } = ();
$t{sth} = $$pref{dbh}->prepare("SELECT id,DWG FROM main_type1 WHERE DWG LIKE \'=\%\'");
$t{sth}->execute;
while ( @rec = $t{sth}->fetchrow_array ) {
push(@{ $t{ids} }, $rec[0]);
$t{dwg1} = "XXXDWG" . $rec[1];
push(@{ $t{DWGs} }, $t{dwg1});
}
$t{sth}->finish;
# 置换
for $n ( 0 .. $#{ $t{ids} } ) {
$t{id1} = $t{ids}[$n];
$t{dwg1} = $t{DWGs}[$n];
$t{sql} = 'UPDATE main_type1 SET DWG = "';
$t{sql} .= $t{dwg1} . '" where id = ';
$t{sql} .= $t{id1};
$t{DO} = $$pref{dbh}->do($t{sql});
print "$t{id1}=>$t{sql}=>DO=$t{DO}\n";
}
# 关闭数据库
$$pref{dbh}->disconnect;
-----------------------------------------
DWG: =SC-2884-1
DWG: XXXDWG=D10439
UPDATE main_type1 SET DWG = "XXXDWG=SC-2884-1" WHERE id = 2421;
UPDATE main_type1 SET DWG = "XXXDWG" WHERE DWG is NULL;
-----------------------------------------
]]>
quo2和enq2的Delivery time合并(取消)
<![CDATA[
mysql> show columns from quo2;
+-----------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| time | date | YES | | NULL | |
| ORIGINid | int(11) | YES | | NULL | |
| PRICEid | int(11) | YES | | NULL | |
| PAYMENTid | int(11) | YES | | NULL | |
| DELIVERY | text | YES | | NULL | |
| money | int(11) | YES | | NULL | |
| percent0 | int(11) | YES | | NULL | |
| discount0 | int(11) | YES | | NULL | |
| percent | text | YES | | NULL | |
| discount | text | YES | | NULL | |
| price | text | YES | | NULL | |
| D_FEE | text | YES | | NULL | |
| total | text | YES | | NULL | |
| memo | text | YES | | NULL | |
| disc0 | int(11) | YES | | NULL | |
| disc | text | YES | | NULL | |
| price0 | text | YES | | NULL | |
+-----------+---------+------+-----+---------+----------------+
18 rows in set (0.02 sec)
mysql> show columns from enq2;
+------------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| time | date | YES | | NULL | |
| enq1id | int(11) | YES | | NULL | |
| LANGUAGEid | int(11) | YES | | NULL | |
| ORIGINid | int(11) | YES | | NULL | |
| PRICEid | int(11) | YES | | NULL | |
| PAYMENTid | int(11) | YES | | NULL | |
| makerid | int(11) | YES | | NULL | |
| DELIVERY | text | YES | | NULL | |
| type1id | text | YES | | NULL | |
| partsid | text | YES | | NULL | |
| QTY | text | YES | | NULL | |
| memo | text | YES | | NULL | |
| price | text | YES | | NULL | |
| discount | text | YES | | NULL | |
| discount0 | int(11) | YES | | NULL | |
| money | int(11) | YES | | NULL | |
| price1 | text | YES | | NULL | |
| makerref | text | YES | | NULL | |
| disc | text | YES | | NULL | |
| charges | text | YES | | NULL | |
+------------+---------+------+-----+---------+----------------+
21 rows in set (0.05 sec)
]]>
返回