~drizzle-trunk/drizzle/development

delete t1, t2 from t1 left join t2 on t1.number=t2.number where (t1.carrier_id=90 and t1.number=t2.number) or (t2.carrier_id=90 and t1.number=t2.number) or (t1.carrier_id=90 and t2.number is null);

823

select * from t1;

824

number cname carrier_id privacy last_mod_date last_mod_id last_app_date last_app_id version assigned_scps status

825

6014911113 SudzCarwash 520 1 2002-01-02 11:52:34 500 2002-01-02 11:52:59 501 33 32768 0

826

333 tubs 99 2 2002-01-09 11:34:40 501 2002-01-09 11:34:40 500 3 10 0

827

select * from t2;

828

number cname carrier_id privacy last_mod_date last_mod_id last_app_date last_app_id version assigned_scps status

829

333 tubs 99 2 2002-01-09 11:34:53 501 2002-01-09 11:34:53 500 3 10 0

830

select * from t2;

831

number cname carrier_id privacy last_mod_date last_mod_id last_app_date last_app_id version assigned_scps status

832

333 tubs 99 2 2002-01-09 11:34:53 501 2002-01-09 11:34:53 500 3 10 0

833

drop table t1,t2;

834

772

create TEMPORARY table t1 (id int not null auto_increment, code int not null, name char(20) not null, primary key (id), key (code), unique (name)) engine=MyISAM;

835

773

BEGIN;

836

774

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

869

807

7 4 Matt

870

808

COMMIT;

871

809

DROP TABLE t1;

872

create TEMPORARY table t1 (n int, d int) engine=MyISAM;

873

create TEMPORARY table t2 (n int, d int) engine=MyISAM;

874

insert into t1 values(1,1),(1,2);

875

insert into t2 values(1,10),(2,20);

876

UPDATE t1,t2 SET t1.d=t2.d,t2.d=30 WHERE t1.n=t2.n;

877

select * from t1;

878

n d

879

1 10

880

1 10

881

select * from t2;

882

n d

883

1 30

884

2 20

885

drop table t1,t2;

886

create TEMPORARY table t1 (a varchar(10) not null) engine = MEMORY;

887

create TEMPORARY table t2 (b varchar(10) not null unique) engine=MyISAM;

888

select t1.a from t1,t2 where t1.a=t2.b;

889

890

drop table t1,t2;

891

create TEMPORARY table t1 (a int not null, b int, primary key (a)) engine = MyISAM;

892

create TEMPORARY table t2 (a int not null, b int, primary key (a)) engine = MyISAM;

893

insert into t1 values (10, 20);

894

insert into t2 values (10, 20);

895

update t1, t2 set t1.b = 150, t2.b = t1.b where t2.a = t1.a and t1.a = 10;

896

drop table t1,t2;

897

810

create TEMPORARY table t1 (a int not null primary key, b int not null, unique (b)) engine=MyISAM;

898

811

INSERT INTO t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);

899

812

UPDATE t1 set a=a+100 where b between 2 and 3 and a < 1000;

909

822

8 8

910

823

9 9

911

824

drop table t1;

912

create TEMPORARY table t1 (a int not null primary key, b int not null, key (b)) engine=MyISAM;

913

create TEMPORARY table t2 (a int not null primary key, b int not null, key (b)) engine=MyISAM;

914

INSERT INTO t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9),(10,10),(11,11),(12,12);

915

INSERT INTO t2 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);

916

update t1,t2 set t1.a=t1.a+100;

917

select * from t1;

918

a b

919

101 1

920

102 2

921

103 3

922

104 4

923

105 5

924

106 6

925

107 7

926

108 8

927

109 9

928

110 10

929

111 11

930

112 12

931

update t1,t2 set t1.a=t1.a+100 where t1.a=101;

932

select * from t1;

933

a b

934

201 1

935

102 2

936

103 3

937

104 4

938

105 5

939

106 6

940

107 7

941

108 8

942

109 9

943

110 10

944

111 11

945

112 12

946

update t1,t2 set t1.b=t1.b+10 where t1.b=2;

947

select * from t1;

948

a b

949

201 1

950

102 12

951

103 3

952

104 4

953

105 5

954

106 6

955

107 7

956

108 8

957

109 9

958

110 10

959

111 11

960

112 12

961

update t1,t2 set t1.b=t1.b+2,t2.b=t1.b+10 where t1.b between 3 and 5 and t1.a=t2.a+100;

962

select * from t1;

963

a b

964

201 1

965

102 12

966

103 5

967

104 6

968

105 7

969

106 6

970

107 7

971

108 8

972

109 9

973

110 10

974

111 11

975

112 12

976

select * from t2;

977

a b

978

1 1

979

2 2

980

3 13

981

4 14

982

5 15

983

6 6

984

7 7

985

8 8

986

9 9

987

drop table t1,t2;

988

create TEMPORARY table t2 ( NEXT_T BIGINT NOT NULL PRIMARY KEY) ENGINE=MEMORY;

989

create TEMPORARY table t1 ( B_ID INTEGER NOT NULL PRIMARY KEY) ENGINE=MyISAM;

990

SET AUTOCOMMIT=0;

991

INSERT INTO t1 ( B_ID ) VALUES ( 1 );

992

INSERT INTO t2 ( NEXT_T ) VALUES ( 1 );

993

ROLLBACK;

994

Warnings:

995

Warning 1196 Some non-transactional changed tables couldn't be rolled back

996

SELECT * FROM t1;

997

B_ID

998

999

drop table t1,t2;

1000

create TEMPORARY table t1 ( pk int primary key, parent int not null, child int not null, index (parent) ) engine = MyISAM;

1001

insert into t1 values (1,0,4), (2,1,3), (3,2,1), (4,1,2);

1002

select distinct parent,child from t1 order by parent;

1003

parent child

1004

0 4

1005

1 2

1006

1 3

1007

2 1

1008

drop table t1;

1009

825

create TEMPORARY table t1 (a int not null auto_increment primary key, b int, c int, key(c)) engine=MyISAM;

1010

826

create TEMPORARY table t2 (a int not null auto_increment primary key, b int) ENGINE = MEMORY;

1011

827

insert into t1 (b) values (null),(null),(null),(null),(null),(null),(null);

1071

887

111 100

1072

888

111 100

1073

889

drop table t1;

1074

create TEMPORARY table t1 ( c char(8) not null ) engine=MyISAM;

1075

insert into t1 values ('0'),('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9');

1076

insert into t1 values ('A'),('B'),('C'),('D'),('E'),('F');

1077

alter table t1 add b char(8) not null;

1078

alter table t1 add a char(8) not null;

1079

alter table t1 add primary key (a,b,c);

1080

update t1 set a=c, b=c;

1081

create TEMPORARY table t2 (c char(8) not null, b char(8) not null, a char(8) not null, primary key(a,b,c)) engine=MyISAM;

1082

insert into t2 select * from t1;

1083

delete t1,t2 from t2,t1 where t1.a<'B' and t2.b=t1.b;

1084

drop table t1,t2;

1085

890

SET AUTOCOMMIT=1;

1086

891

create TEMPORARY table t1 (a integer auto_increment primary key) engine=MyISAM;

1087

892

insert into t1 (a) values (NULL),(NULL);

1886

1691

GRADE

1887

1692

151

1888

1693

DROP TABLE t1;

1889

create TEMPORARY table t1 (f1 varchar(10), f2 varchar(10), primary key (f1,f2)) engine=MyISAM;

1890

create TEMPORARY table t2 (f3 varchar(10), f4 varchar(10), key (f4)) engine=MyISAM;

1891

insert into t2 values ('aa','cc');

1892

insert into t1 values ('aa','bb'),('aa','cc');

1893

delete t1 from t1,t2 where f1=f3 and f4='cc';

1894

select * from t1;

1895

f1 f2

1896

drop table t1,t2;

1897

1694

create TEMPORARY table t1(a date) engine=MyISAM;

1898

1695

create TEMPORARY table t2(a date, key(a)) engine=MyISAM;

1899

1696

insert into t1 values('2005-10-01');

1912

1709

INSERT INTO t2 VALUES (1, 0, '');

1913

1710

INSERT INTO t2 VALUES (8, 1, '');

1914

1711

commit;

1915

DELETE ml.* FROM t1 AS ml LEFT JOIN t2 AS mm ON (mm.id=ml.id)

1916

WHERE mm.id IS NULL;

1917

1712

select ml.* from t1 as ml left join t2 as mm on (mm.id=ml.id)

1918

1713

where mm.id is null lock in share mode;

1919

1714

id f_id f

Older »