update.result 3.07 KB
Newer Older
1
drop table if exists t1,t2;
2 3 4 5 6 7 8 9 10 11 12 13
create table t1 (a int auto_increment , primary key (a));
insert into t1 values (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL);
update t1 set a=a+10 where a > 34;
update t1 set a=a+100 where a > 0;
update t1 set a=a+100 where a=1 and a=2;
update t1 set a=b+100 where a=1 and a=2;
Unknown column 'b' in 'field list'
update t1 set a=b+100 where c=1 and a=2;
Unknown column 'c' in 'where clause'
update t1 set d=a+100 where a=1;
Unknown column 'd' in 'field list'
select * from t1;
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 48 49 50
a
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
145
146
51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67
drop table t1;
CREATE TABLE t1
(
place_id int (10) unsigned NOT NULL,
shows int(10) unsigned DEFAULT '0' NOT NULL,
ishows int(10) unsigned DEFAULT '0' NOT NULL,
ushows int(10) unsigned DEFAULT '0' NOT NULL,
clicks int(10) unsigned DEFAULT '0' NOT NULL,
iclicks int(10) unsigned DEFAULT '0' NOT NULL,
uclicks int(10) unsigned DEFAULT '0' NOT NULL,
ts timestamp(14),
PRIMARY KEY (place_id,ts)
);
INSERT INTO t1 (place_id,shows,ishows,ushows,clicks,iclicks,uclicks,ts)
VALUES (1,0,0,0,0,0,0,20000928174434);
UPDATE t1 SET shows=shows+1,ishows=ishows+1,ushows=ushows+1,clicks=clicks+1,iclicks=iclicks+1,uclicks=uclicks+1 WHERE place_id=1 AND ts>="2000-09-28 00:00:00";
select place_id,shows from t1;
68 69
place_id	shows
1	1
70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98
drop table t1;
CREATE TABLE t1 (
lfdnr int(10) unsigned NOT NULL default '0',
ticket int(10) unsigned NOT NULL default '0',
client varchar(255) NOT NULL default '',
replyto varchar(255) NOT NULL default '',
subject varchar(100) NOT NULL default '',
timestamp int(10) unsigned NOT NULL default '0',
tstamp timestamp(14) NOT NULL,
status int(3) NOT NULL default '0',
type varchar(15) NOT NULL default '',
assignment int(10) unsigned NOT NULL default '0',
fupcount int(4) unsigned NOT NULL default '0',
parent int(10) unsigned NOT NULL default '0',
activity int(10) unsigned NOT NULL default '0',
priority tinyint(1) unsigned NOT NULL default '1',
cc varchar(255) NOT NULL default '',
bcc varchar(255) NOT NULL default '',
body text NOT NULL,
comment text,
header text,
PRIMARY KEY  (lfdnr),
KEY k1 (timestamp),
KEY k2 (type),
KEY k3 (parent),
KEY k4 (assignment),
KEY ticket (ticket)
) TYPE=MyISAM;
INSERT INTO t1 VALUES (773,773,'','','',980257344,20010318180652,0,'Open',10,0,0,0,1,'','','','','');
99
alter table t1 change lfdnr lfdnr int(10) unsigned not null auto_increment;
100 101
update t1 set status=1 where type='Open';
select status from t1;
102 103
status
1
104
drop table t1;
105 106 107 108
create table t1 (a int not null, b int not null);
insert into t1 values (1,1),(1,2),(1,3);
update t1 set b=4 where a=1 order by b asc limit 1;
update t1 set b=4 where a=1 order by b desc limit 1;
109 110
create table t2 (a int not null, b int not null);
insert into t2 values (1,1),(1,2),(1,3);
111 112 113 114
select * from t1;
a	b
1	4
1	2
serg@serg.mysql.com's avatar
serg@serg.mysql.com committed
115
1	4
116 117
update t1 set b=(select distinct 1 from (select * from t2) a);
drop table t1,t2;