~drizzle-trunk/drizzle/development

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near '1=2 WHERE t1.n=t2.n' at line 1

194

drop table t1,t2;

195

set timestamp=0;

196

set sql_safe_updates=0;

197

create table t1 (n int not null primary key, d int);

198

create table t2 (n int not null primary key, d int);

199

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

200

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

201

UPDATE t2 left outer join t1 on t1.n=t2.n SET t1.d=t2.d;

202

select * from t1;

203

n d

204

1 10

205

3 3

206

select * from t2;

207

n d

208

1 10

209

2 20

210

drop table t1,t2;

211

create table t1 (n int, d int);

212

create table t2 (n int, d int);

213

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

214

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

215

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

216

select * from t1;

217

n d

218

1 10

219

1 10

220

select * from t2;

221

n d

222

1 30

223

2 20

224

drop table t1,t2;

225

create table t1 (n int, d int);

226

create table t2 (n int, d int);

227

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

228

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

229

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

230

select * from t1;

231

n d

232

1 10

233

3 2

234

select * from t2;

235

n d

236

1 30

237

1 30

238

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

239

select * from t1;

240

n d

241

1 30

242

3 2

243

select * from t2;

244

n d

245

1 30

246

1 30

247

DELETE a, b FROM t1 a,t2 b where a.n=b.n;

248

select * from t1;

249

n d

250

3 2

251

select * from t2;

252

n d

253

drop table t1,t2;

254

CREATE TABLE t1 ( broj int NOT NULL default '0', naziv char(25) NOT NULL default 'NEPOZNAT', PRIMARY KEY (broj)) ENGINE=MyISAM;

255

INSERT INTO t1 VALUES (1,'jedan'),(2,'dva'),(3,'tri'),(4,'xxxxxxxxxx'),(5,'a'),(10,''),(11,''),(12,''),(13,'');

256

CREATE TABLE t2 ( broj int NOT NULL default '0', naziv char(25) NOT NULL default 'NEPOZNAT', PRIMARY KEY (broj)) ENGINE=MyISAM;

257

INSERT INTO t2 VALUES (1,'jedan'),(2,'dva'),(3,'tri'),(4,'xxxxxxxxxx'),(5,'a');

258

CREATE TABLE t3 ( broj int NOT NULL default '0', naziv char(25) NOT NULL default 'NEPOZNAT', PRIMARY KEY (broj)) ENGINE=MyISAM;

259

INSERT INTO t3 VALUES (1,'jedan'),(2,'dva');

260

update t1,t2 set t1.naziv="aaaa" where t1.broj=t2.broj;

261

update t1,t2,t3 set t1.naziv="bbbb", t2.naziv="aaaa" where t1.broj=t2.broj and t2.broj=t3.broj;

262

drop table t1,t2,t3;

263

CREATE TABLE t1 (a int not null primary key, b int not null, key (b));

264

CREATE TABLE t2 (a int not null primary key, b int not null, key (b));

265

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

266

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

267

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

268

select * from t1;

269

a b

270

101 1

271

102 2

272

103 3

273

104 4

274

105 5

275

106 6

276

107 7

277

108 8

278

109 9

279

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

280

select * from t1;

281

a b

282

102 2

283

103 3

284

104 4

285

105 5

286

106 6

287

107 7

288

108 8

289

109 9

290

201 1

291

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

292

select * from t1;

293

a b

294

102 12

295

103 3

296

104 4

297

105 5

298

106 6

299

107 7

300

108 8

301

109 9

302

201 1

303

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

304

select * from t1;

305

a b

306

102 12

307

103 5

308

104 6

309

105 7

310

106 6

311

107 7

312

108 8

313

109 9

314

201 1

315

select * from t2;

316

a b

317

1 1

318

2 2

319

3 15

320

4 16

321

5 17

322

6 6

323

7 7

324

8 8

325

9 9

326

update t1,t2 set t1.b=t2.b, t1.a=t2.a where t1.a=t2.a and not exists (select * from t2 where t2.a > 10);

327

drop table t1,t2;

328

CREATE TABLE t3 ( KEY1 varchar(50) NOT NULL default '', PARAM_CORR_DISTANCE_RUSH double default NULL, PARAM_CORR_DISTANCE_GEM double default NULL, PARAM_AVG_TARE double default NULL, PARAM_AVG_NB_DAYS double default NULL, PARAM_DEFAULT_PROP_GEM_SRVC varchar(50) default NULL, PARAM_DEFAULT_PROP_GEM_NO_ETIK varchar(50) default NULL, PARAM_SCENARIO_COSTS varchar(50) default NULL, PARAM_DEFAULT_WAGON_COST double default NULL, tmp int default NULL, PRIMARY KEY (KEY1)) ENGINE=MyISAM;

329

INSERT INTO t3 VALUES ('A',1,1,22,3.2,'R','R','BASE2',0.24,NULL);

330

create table t1 (A varchar(1));

331

insert into t1 values ("A") ,("B"),("C"),("D");

332

create table t2(Z varchar(15));

333

insert into t2(Z) select concat(a.a,b.a,c.a,d.a) from t1 as a, t1 as b, t1 as c, t1 as d;

334

update t2,t3 set Z =param_scenario_costs;

335

drop table t1,t2,t3;

336

create table t1 (a int, b int);

337

create table t2 (a int, b int);

338

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

339

insert into t2 values (1,1), (3,1);

340

update t1 left join t2 on t1.a=t2.a set t1.b=2, t2.b=2 where t1.b=1 and t2.b=1 or t2.a is NULL;

341

select t1.a, t1.b,t2.a, t2.b from t1 left join t2 on t1.a=t2.a where t1.b=1 and t2.b=1 or t2.a is NULL;

342

a b a b

343

2 2 NULL NULL

344

drop table t1,t2;

345

create table t1 (a int not null auto_increment primary key, b int not null);

346

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

347

update t1, t1 as t2 set t1.b=t2.b+1 where t1.a=t2.a;

348

select * from t1;

349

a b

350

1 2

351

2 3

352

3 4

353

4 5

354

drop table t1;

355

create table t1(id1 int, field char(5));

356

create table t2(id2 int, field char(5));

357

insert into t1 values (1, 'a'), (2, 'aa');

358

insert into t2 values (1, 'b'), (2, 'bb');

359

select * from t1;

360

id1 field

361

1 a

362

2 aa

363

select * from t2;

364

id2 field

365

1 b

366

2 bb

367

update t2 inner join t1 on t1.id1=t2.id2

368

set t2.field=t1.field

369

where 0=1;

370

update t2, t1 set t2.field=t1.field

371

where t1.id1=t2.id2 and 0=1;

372

delete t1, t2 from t2 inner join t1 on t1.id1=t2.id2

373

where 0=1;

374

delete t1, t2 from t2,t1

375

where t1.id1=t2.id2 and 0=1;

376

drop table t1,t2;

377

create table t1 ( a int not null, b int not null) ;

378

alter table t1 add index i1(a);

379

delete from t1 where a > 2000000;

380

create table t2 like t1;

381

insert into t2 select * from t1;

382

select 't2 rows before small delete', count(*) from t1;

383

t2 rows before small delete count(*)

384

t2 rows before small delete 2000000

385

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

386

select 't2 rows after small delete', count(*) from t2;

387

t2 rows after small delete count(*)

388

t2 rows after small delete 1999999

389

select 't1 rows after small delete', count(*) from t1;

390

t1 rows after small delete count(*)

391

t1 rows after small delete 1999999

392

delete t1,t2 from t1,t2 where t1.b=t2.a and t1.a < 100*1000;

393

select 't2 rows after big delete', count(*) from t2;

394

t2 rows after big delete count(*)

395

t2 rows after big delete 1900001

396

select 't1 rows after big delete', count(*) from t1;

397

t1 rows after big delete count(*)

398

t1 rows after big delete 1900001

399

drop table t1,t2;

400

CREATE TABLE t1 ( a int );

401

CREATE TABLE t2 ( a int );

402

DELETE t1 FROM t1, t2 AS t3;

403

DELETE t4 FROM t1, t1 AS t4;

404

DELETE t3 FROM t1 AS t3, t1 AS t4;

405

DELETE t1 FROM t1 AS t3, t2 AS t4;

406

ERROR 42S02: Unknown table 't1' in MULTI DELETE

407

INSERT INTO t1 values (1),(2);

408

INSERT INTO t2 values (1),(2);

409

DELETE t1 FROM t1 AS t2, t2 AS t1 where t1.a=t2.a and t1.a=1;

410

SELECT * from t1;

411

412

413

414

SELECT * from t2;

415

416

417

DELETE t2 FROM t1 AS t2, t2 AS t1 where t1.a=t2.a and t1.a=2;

418

SELECT * from t1;

419

420

421

SELECT * from t2;

422

423

424

DROP TABLE t1,t2;

425

create table `t1` (`p_id` int NOT NULL auto_increment, `p_code` varchar(20) NOT NULL default '', `p_active` int NOT NULL default '1', PRIMARY KEY (`p_id`) );

426

create table `t2` (`c2_id` int NULL auto_increment, `c2_p_id` int NOT NULL default '0', `c2_note` text NOT NULL, `c2_active` int NOT NULL default '1', PRIMARY KEY (`c2_id`), KEY `c2_p_id` (`c2_p_id`) );

427

insert into t1 values (0,'A01-Comp',1);

428

insert into t1 values (0,'B01-Comp',1);

429

insert into t2 values (0,1,'A Note',1);

430

update t1 left join t2 on p_id = c2_p_id set c2_note = 'asdf-1' where p_id = 2;

431

select * from t1;

432

p_id p_code p_active

433

1 A01-Comp 1

434

2 B01-Comp 1

435

select * from t2;

436

c2_id c2_p_id c2_note c2_active

437

1 1 A Note 1

438

drop table t1, t2;

439

create table t1 (a int, primary key (a));

440

create table t2 (a int, primary key (a));

441

create table t3 (a int, primary key (a));

442

delete t1,t3 from t1,t2 where t1.a=t2.a and t2.a=(select t3.a from t3 where t1.a=t3.a);

443

ERROR 42S02: Unknown table 't3' in MULTI DELETE

444

drop table t1, t2, t3;

445

create table t1 (col1 int);

446

create table t2 (col1 int);

447

update t1,t2 set t1.col1 = (select max(col1) from t1) where t1.col1 = t2.col1;

448

ERROR HY000: You can't specify target table 't1' for update in FROM clause

449

delete t1 from t1,t2 where t1.col1 < (select max(col1) from t1) and t1.col1 = t2.col1;

450

ERROR HY000: You can't specify target table 't1' for update in FROM clause

451

drop table t1,t2;

452

create table t1 (

453

aclid bigint not null primary key,

454

status int not null

455

) engine = innodb;

456

create table t2 (

457

refid bigint not null primary key,

458

aclid bigint, index idx_acl(aclid)

459

) engine = innodb;

460

insert into t2 values(1,null);

461

delete t2, t1 from t2 left join t1 on (t2.aclid=t1.aclid) where t2.refid='1';

462

drop table t1, t2;

463

create table t1(a int);

464

create table t2(a int);

465

delete from t1,t2 using t1,t2 where t1.a=(select a from t1);

466

ERROR HY000: You can't specify target table 't1' for update in FROM clause

467

drop table t1, t2;

468

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

469

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

470

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

471

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

472

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

473

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

474

update t1 set a=c, b=c;

475

create table t2 like t1;

476

insert into t2 select * from t1;

477

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

478

drop table t1,t2;

479

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

480

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

481

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

482

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

483

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

484

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

485

update t1 set a=c, b=c;

486

create table t2 like t1;

487

insert into t2 select * from t1;

488

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

489

drop table t1,t2;

490

create table t1 (a int, b int);

491

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

492

create table t2 (a int);

493

insert into t2 values (10), (20), (30);

494

create table v1 as select a as b, a/10 as a from t2;

495

lock table t1 write;

496

alter table t1 add column c int default 100 after a;

497

update t1, v1 set t1.b=t1.a+t1.b+v1.b where t1.a=v1.a;

498

unlock tables;

499

select * from t1;

500

a c b

501

1 100 13

502

2 100 25

503

3 100 37

504

select * from t2;

505

506

507

508

509

drop table v1;

510

drop table t1, t2;

511

create table t1 (i1 int, i2 int, i3 int);

512

create table t2 (id int, c1 varchar(20), c2 varchar(20));

513

insert into t1 values (1,5,10),(3,7,12),(4,5,2),(9,10,15),(2,2,2);

514

insert into t2 values (9,"abc","def"),(5,"opq","lmn"),(2,"test t","t test");

515

select * from t1 order by i1;

516

i1 i2 i3

517

1 5 10

518

2 2 2

519

3 7 12

520

4 5 2

521

9 10 15

522

select * from t2;

523

id c1 c2

524

9 abc def

525

5 opq lmn

526

2 test t t test

527

update t1,t2 set t1.i2=15, t2.c2="ppc" where t1.i1=t2.id;

528

select * from t1 order by i1;

529

i1 i2 i3

530

1 5 10

531

2 15 2

532

3 7 12

533

4 5 2

534

9 15 15

535

select * from t2 order by id;

536

id c1 c2

537

2 test t ppc

538

5 opq lmn

539

9 abc ppc

540

delete t1.*,t2.* from t1,t2 where t1.i2=t2.id;

541

select * from t1 order by i1;

542

i1 i2 i3

543

2 15 2

544

3 7 12

545

9 15 15

546

select * from t2 order by id;

547

id c1 c2

548

2 test t ppc

549

9 abc ppc

550

drop table t1, t2;

551

create table t1 (i1 int auto_increment not null, i2 int, i3 int, primary key (i1));

552

create table t2 (id int auto_increment not null, c1 varchar(20), c2 varchar(20), primary key(id));

553

insert into t1 values (1,5,10),(3,7,12),(4,5,2),(9,10,15),(2,2,2);

554

insert into t2 values (9,"abc","def"),(5,"opq","lmn"),(2,"test t","t test");

555

select * from t1 order by i1;

556

i1 i2 i3

557

1 5 10

558

2 2 2

559

3 7 12

560

4 5 2

561

9 10 15

562

select * from t2 order by id;

563

id c1 c2

564

2 test t t test

565

5 opq lmn

566

9 abc def

567

update t1,t2 set t1.i2=15, t2.c2="ppc" where t1.i1=t2.id;

568

select * from t1 order by i1;

569

i1 i2 i3

570

1 5 10

571

2 15 2

572

3 7 12

573

4 5 2

574

9 15 15

575

select * from t2 order by id;

576

id c1 c2

577

2 test t ppc

578

5 opq lmn

579

9 abc ppc

580

delete t1.*,t2.* from t1,t2 where t1.i2=t2.id;

581

select * from t1 order by i1;

582

i1 i2 i3

583

2 15 2

584

3 7 12

585

9 15 15

586

select * from t2 order by id;

587

id c1 c2

588

2 test t ppc

589

9 abc ppc

590

drop table t1, t2;

591

end of tests

Older »