1
drop table if exists t1,t2,t3;
2
create table t1 (bandID MEDIUMINT UNSIGNED NOT NULL PRIMARY KEY, payoutID SMALLINT UNSIGNED NOT NULL);
3
insert into t1 (bandID,payoutID) VALUES (1,6),(2,6),(3,4),(4,9),(5,10),(6,1),(7,12),(8,12);
4
create table t2 (payoutID SMALLINT UNSIGNED NOT NULL PRIMARY KEY);
5
insert into t2 (payoutID) SELECT DISTINCT payoutID FROM t1;
6
insert into t2 (payoutID) SELECT payoutID+10 FROM t1;
7
ERROR 23000: Duplicate entry '16' for key 'PRIMARY'
8
insert ignore into t2 (payoutID) SELECT payoutID+10 FROM t1;
25
`numeropost` bigint(20) unsigned NOT NULL default '0',
26
`icone` tinyint(4) unsigned NOT NULL default '0',
27
`numreponse` bigint(20) unsigned NOT NULL auto_increment,
28
`contenu` text NOT NULL,
29
`pseudo` varchar(50) NOT NULL default '',
30
`date` datetime NOT NULL default '0000-00-00 00:00:00',
31
`ip` bigint(11) NOT NULL default '0',
32
`signature` tinyint(1) unsigned NOT NULL default '0',
33
PRIMARY KEY (`numeropost`,`numreponse`)
36
KEY `pseudo` (`pseudo`),
37
KEY `numreponse` (`numreponse`)
40
`numeropost` bigint(20) unsigned NOT NULL default '0',
41
`icone` tinyint(4) unsigned NOT NULL default '0',
42
`numreponse` bigint(20) unsigned NOT NULL auto_increment,
43
`contenu` text NOT NULL,
44
`pseudo` varchar(50) NOT NULL default '',
45
`date` datetime NOT NULL default '0000-00-00 00:00:00',
46
`ip` bigint(11) NOT NULL default '0',
47
`signature` tinyint(1) unsigned NOT NULL default '0',
48
PRIMARY KEY (`numeropost`,`numreponse`),
51
KEY `pseudo` (`pseudo`),
52
KEY `numreponse` (`numreponse`)
55
(numeropost,icone,numreponse,contenu,pseudo,date,ip,signature) VALUES
56
(9,1,56,'test','joce','2001-07-25 13:50:53'
58
INSERT INTO t1 (numeropost,icone,contenu,pseudo,date,signature,ip)
59
SELECT 1618,icone,contenu,pseudo,date,signature,ip FROM t2
60
WHERE numeropost=9 ORDER BY numreponse ASC;
61
show variables like '%bulk%';
63
bulk_insert_buffer_size 8388608
64
INSERT INTO t1 (numeropost,icone,contenu,pseudo,date,signature,ip)
65
SELECT 1718,icone,contenu,pseudo,date,signature,ip FROM t2
66
WHERE numeropost=9 ORDER BY numreponse ASC;
68
create table t1 (a int not null);
69
create table t2 (a int not null);
70
insert into t1 values (1);
71
insert into t1 values (a+2);
72
insert into t1 values (a+3);
73
insert into t1 values (4),(a+5);
74
insert into t1 select * from t1;
87
insert into t1 select * from t1 as t2;
110
insert into t2 select * from t1 as t2;
133
insert into t1 select t2.a from t1,t2;
556
insert into t1 select * from t1,t1;
557
ERROR 42000: Not unique table/alias: 't1'
559
create table t1 (a int not null primary key, b char(10));
560
create table t2 (a int not null, b char(10));
561
insert into t1 values (1,"t1:1"),(3,"t1:3");
562
insert into t2 values (2,"t2:2"), (3,"t2:3");
563
insert into t1 select * from t2;
564
ERROR 23000: Duplicate entry '3' for key 'PRIMARY'
569
replace into t1 select * from t2;
576
CREATE TABLE t1 ( USID INTEGER UNSIGNED, ServerID TINYINT UNSIGNED, State ENUM ('unknown', 'Access-Granted', 'Session-Active', 'Session-Closed' ) NOT NULL DEFAULT 'unknown', SessionID CHAR(32), User CHAR(32) NOT NULL DEFAULT '<UNKNOWN>', NASAddr INTEGER UNSIGNED, NASPort INTEGER UNSIGNED, NASPortType INTEGER UNSIGNED, ConnectSpeed INTEGER UNSIGNED, CarrierType CHAR(32), CallingStationID CHAR(32), CalledStationID CHAR(32), AssignedAddr INTEGER UNSIGNED, SessionTime INTEGER UNSIGNED, PacketsIn INTEGER UNSIGNED, OctetsIn INTEGER UNSIGNED, PacketsOut INTEGER UNSIGNED, OctetsOut INTEGER UNSIGNED, TerminateCause INTEGER UNSIGNED, UnauthTime TINYINT UNSIGNED, AccessRequestTime DATETIME, AcctStartTime DATETIME, AcctLastTime DATETIME, LastModification TIMESTAMP NOT NULL);
577
CREATE TABLE t2 ( USID INTEGER UNSIGNED AUTO_INCREMENT, ServerID TINYINT UNSIGNED, State ENUM ('unknown', 'Access-Granted', 'Session-Active', 'Session-Closed' ) NOT NULL DEFAULT 'unknown', SessionID CHAR(32), User TEXT NOT NULL, NASAddr INTEGER UNSIGNED, NASPort INTEGER UNSIGNED, NASPortType INTEGER UNSIGNED, ConnectSpeed INTEGER UNSIGNED, CarrierType CHAR(32), CallingStationID CHAR(32), CalledStationID CHAR(32), AssignedAddr INTEGER UNSIGNED, SessionTime INTEGER UNSIGNED, PacketsIn INTEGER UNSIGNED, OctetsIn INTEGER UNSIGNED, PacketsOut INTEGER UNSIGNED, OctetsOut INTEGER UNSIGNED, TerminateCause INTEGER UNSIGNED, UnauthTime TINYINT UNSIGNED, AccessRequestTime DATETIME, AcctStartTime DATETIME, AcctLastTime DATETIME, LastModification TIMESTAMP NOT NULL, INDEX(USID,ServerID,NASAddr,SessionID), INDEX(AssignedAddr));
578
INSERT INTO t1 VALUES (39,42,'Access-Granted','46','491721000045',2130706433,17690,NULL,NULL,'Localnet','491721000045','49172200000',754974766,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2003-07-18 00:11:21',NULL,NULL,20030718001121);
579
INSERT INTO t2 SELECT USID, ServerID, State, SessionID, User, NASAddr, NASPort, NASPortType, ConnectSpeed, CarrierType, CallingStationID, CalledStationID, AssignedAddr, SessionTime, PacketsIn, OctetsIn, PacketsOut, OctetsOut, TerminateCause, UnauthTime, AccessRequestTime, AcctStartTime, AcctLastTime, LastModification from t1 LIMIT 1;
583
Type tinyint(3) unsigned NOT NULL auto_increment,
584
Field int(10) unsigned NOT NULL,
585
Count int(10) unsigned NOT NULL,
586
UNIQUE KEY Month (Month,Type,Field)
588
insert into t1 Values
589
(20030901, 1, 1, 100),
590
(20030901, 1, 2, 100),
591
(20030901, 2, 1, 100),
592
(20030901, 2, 2, 100),
593
(20030901, 3, 1, 100);
595
Month Type Field Count
601
Select null, Field, Count From t1 Where Month=20030901 and Type=2;
605
create table t2(No int not null, Field int not null, Count int not null);
606
insert into t2 Select null, Field, Count From t1 Where Month=20030901 and Type=2;
608
Warning 1048 Column 'No' cannot be null
609
Warning 1048 Column 'No' cannot be null
616
ID int(11) NOT NULL auto_increment,
617
NO int(11) NOT NULL default '0',
618
SEQ int(11) NOT NULL default '0',
622
INSERT INTO t1 (SEQ, NO) SELECT "1" AS SEQ, IF(MAX(NO) IS NULL, 0, MAX(NO)) + 1 AS NO FROM t1 WHERE (SEQ = 1);
623
select SQL_BUFFER_RESULT * from t1 WHERE (SEQ = 1);
627
create table t1 (f1 int);
628
create table t2 (ff1 int unique, ff2 int default 1);
629
insert into t1 values (1),(1),(2);
630
insert into t2(ff1) select f1 from t1 on duplicate key update ff2=ff2+1;
636
create table t1 (a int unique);
637
create table t2 (a int, b int);
638
create table t3 (c int, d int);
639
insert into t1 values (1),(2);
640
insert into t2 values (1,2);
641
insert into t3 values (1,6),(3,7);
646
insert into t1 select a from t2 on duplicate key update a= t1.a + t2.b;
651
insert into t1 select a+1 from t2 on duplicate key update t1.a= t1.a + t2.b+1;
656
insert into t1 select t3.c from t3 on duplicate key update a= a + t3.d;
662
insert into t1 select t2.a from t2 group by t2.a on duplicate key update a= a + 10;
663
insert into t1 select t2.a from t2 on duplicate key update a= a + t2.b;
664
ERROR 23000: Column 'a' in field list is ambiguous
665
insert into t1 select t2.a from t2 on duplicate key update t2.a= a + t2.b;
666
ERROR 42S22: Unknown column 't2.a' in 'field list'
667
insert into t1 select t2.a from t2 group by t2.a on duplicate key update a= t1.a + t2.b;
668
ERROR 42S22: Unknown column 't2.b' in 'field list'
670
create table t1(f1 varchar(5) key);
671
insert into t1(f1) select if(max(f1) is null, '2000',max(f1)+1) from t1;
672
insert into t1(f1) select if(max(f1) is null, '2000',max(f1)+1) from t1;
673
insert into t1(f1) select if(max(f1) is null, '2000',max(f1)+1) from t1;
680
create table t1(x int, y int);
681
create table t2(x int, z int);
682
insert into t1(x,y) select x,z from t2 on duplicate key update x=values(x);
683
insert into t1(x,y) select x,z from t2 on duplicate key update x=values(z);
684
ERROR 42S22: Unknown column 'z' in 'field list'
685
insert into t1(x,y) select x,z from t2 on duplicate key update x=values(t2.x);
686
ERROR 42S22: Unknown column 't2.x' in 'field list'
688
CREATE TABLE t1 (a int PRIMARY KEY);
689
INSERT INTO t1 values (1), (2);
690
INSERT INTO t1 SELECT a + 2 FROM t1 LIMIT 1;
692
CREATE TABLE t1 (x int, y int);
693
CREATE TABLE t2 (z int, y int);
694
CREATE TABLE t3 (a int, b int);
695
INSERT INTO t3 (SELECT x, y FROM t1 JOIN t2 USING (y) WHERE z = 1);
696
DROP TABLE IF EXISTS t1,t2,t3;
697
CREATE DATABASE meow;
698
CREATE TABLE table_target ( mexs_id CHAR(8), messzeit TIMESTAMP, PRIMARY KEY (mexs_id));
699
CREATE TABLE table_target2 ( mexs_id CHAR(8), messzeit TIMESTAMP, PRIMARY KEY (mexs_id));
700
CREATE TABLE table_target3 ( mexs_id CHAR(8), messzeit TIMESTAMP, PRIMARY KEY (mexs_id));
701
CREATE VIEW view_target2 AS SELECT mexs_id,messzeit FROM table_target2;
702
CREATE SQL SECURITY INVOKER VIEW view_target3 AS SELECT mexs_id,messzeit FROM table_target3;
703
CREATE TABLE table_stations ( mexs_id VARCHAR(8), icao VARCHAR(4), country CHAR(2), PRIMARY KEY (mexs_id), UNIQUE KEY icao (icao), KEY country (country), CONSTRAINT stations_ibfk_8 FOREIGN KEY (country) REFERENCES countries (country) ON UPDATE CASCADE);
704
INSERT INTO table_stations VALUES ('87654321','XXXX','YY');
705
CREATE TABLE table_countries ( country CHAR(2), iso_short_en VARCHAR(64), PRIMARY KEY (country));
706
INSERT INTO table_countries VALUES ('YY','Entenhausen');
707
CREATE ALGORITHM=MERGE SQL SECURITY INVOKER VIEW view_stations AS select table_stations.mexs_id AS mexs_id, table_stations.icao AS icao, table_stations.country AS landescode from (table_stations join table_countries on((table_stations.country = table_countries.country)));
708
CREATE TABLE table_source ( id varchar(4), datetime TIMESTAMP, PRIMARY KEY (id));
709
INSERT INTO table_source VALUES ('XXXX','2006-07-12 07:50:00');
710
GRANT SELECT ON table_source TO user20989@localhost;
711
GRANT SELECT ON table_countries TO user20989@localhost;
712
GRANT SELECT ON table_stations TO user20989@localhost;
713
GRANT SELECT ON view_stations TO user20989@localhost;
714
GRANT SELECT ON table_target TO user20989@localhost;
715
GRANT SELECT ON table_target2 TO user20989@localhost;
716
GRANT INSERT,DELETE,SELECT ON view_target3 TO user20989@localhost;
717
REPLACE INTO table_target
718
SELECT stations.mexs_id AS mexs_id, datetime AS messzeit
720
INNER JOIN view_stations AS stations
721
ON table_source.id = stations.icao
722
LEFT JOIN table_target AS old
724
ERROR 42000: INSERT,DELETE command denied to user 'user20989'@'localhost' for table 'table_target'
725
REPLACE INTO view_target2
726
SELECT stations.mexs_id AS mexs_id, datetime AS messzeit
728
INNER JOIN view_stations AS stations
729
ON table_source.id = stations.icao
730
LEFT JOIN view_target2 AS old
732
ERROR 42000: INSERT,DELETE command denied to user 'user20989'@'localhost' for table 'view_target2'
733
REPLACE INTO view_target3
734
SELECT stations.mexs_id AS mexs_id, datetime AS messzeit
736
INNER JOIN view_stations AS stations
737
ON table_source.id = stations.icao
738
LEFT JOIN view_target3 AS old
740
ERROR HY000: View 'meow.view_target3' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
741
GRANT INSERT,DELETE ON table_target TO user20989@localhost;
742
GRANT INSERT,DELETE,SELECT ON view_target2 TO user20989@localhost;
743
GRANT INSERT,DELETE,SELECT ON table_target3 TO user20989@localhost;
744
REPLACE INTO table_target
745
SELECT stations.mexs_id AS mexs_id, datetime AS messzeit
747
INNER JOIN view_stations AS stations
748
ON table_source.id = stations.icao
749
LEFT JOIN table_target AS old
751
REPLACE INTO table_target2 VALUES ('00X45Y78','2006-07-12 07:50:00');
752
ERROR 42000: INSERT,DELETE command denied to user 'user20989'@'localhost' for table 'table_target2'
753
REPLACE INTO view_target2 VALUES ('12X45Y78','2006-07-12 07:50:00');
754
SELECT stations.mexs_id AS mexs_id, datetime AS messzeit
756
INNER JOIN view_stations AS stations
757
ON table_source.id = stations.icao
758
LEFT JOIN view_target2 AS old
761
87654321 2006-07-12 07:50:00
762
REPLACE INTO view_target2
763
SELECT stations.mexs_id AS mexs_id, datetime AS messzeit
765
INNER JOIN view_stations AS stations
766
ON table_source.id = stations.icao
767
LEFT JOIN view_target2 AS old
769
REPLACE INTO view_target3
770
SELECT stations.mexs_id AS mexs_id, datetime AS messzeit
772
INNER JOIN view_stations AS stations
773
ON table_source.id = stations.icao
774
LEFT JOIN view_target3 AS old
776
SELECT * FROM table_target;
778
87654321 2006-07-12 07:50:00
779
SELECT * FROM view_target2;
781
12X45Y78 2006-07-12 07:50:00
782
87654321 2006-07-12 07:50:00
783
SELECT * FROM view_target3;
785
87654321 2006-07-12 07:50:00
786
DROP VIEW view_stations;
787
DROP TABLE table_source;
788
DROP TABLE table_countries;
789
DROP TABLE table_stations;
790
DROP TABLE table_target;
791
DROP TABLE table_target2;
792
DROP TABLE table_target3;
793
DROP VIEW view_target2;
794
DROP VIEW view_target3;
795
DROP USER user20989@localhost;
added
removed
pbxt/mysql-test-update/mysql-test/r/insert_select.result
