1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
|
#
# test case for #570
#
# This failed because of syntax. Changed int(5) to int.
CREATE TABLE t1 (
aufnr varchar(12) NOT NULL default '',
plnfl varchar(6) NOT NULL default '',
vornr varchar(4) NOT NULL default '',
xstatus_vor int NOT NULL default '0'
);
INSERT INTO t1 VALUES ('40004712','000001','0010',9);
INSERT INTO t1 VALUES ('40004712','000001','0020',0);
UPDATE t1 SET t1.xstatus_vor = Greatest(t1.xstatus_vor,1) WHERE t1.aufnr =
"40004712" AND t1.plnfl = "000001" AND t1.vornr > "0010" ORDER BY t1.vornr
ASC LIMIT 1;
drop table t1;
# and again with a primary key
CREATE TABLE t1 (
aufnr varchar(12) NOT NULL default '',
plnfl varchar(6) NOT NULL default '',
vornr varchar(4) NOT NULL default '' primary key,
xstatus_vor int NOT NULL default '0'
);
INSERT INTO t1 VALUES ('40004712','000001','0010',9);
INSERT INTO t1 VALUES ('40004712','000001','0020',0);
UPDATE t1 SET t1.xstatus_vor = Greatest(t1.xstatus_vor,1) WHERE t1.aufnr =
"40004712" AND t1.plnfl = "000001" AND t1.vornr > "0010" ORDER BY t1.vornr
ASC LIMIT 1;
drop table t1;
# and again with a unique key that becomes the clustered key
create table t1 (a int, b int, c int);
alter table t1 add unique(a,b);
drop table t1;
|