SQL help. - 未名空间MITBBS历史存档

国际科技财经博客移民网络热点娱乐民生时事公众号

Redian新闻

>未名空间

>Database - 数据库

SQL help.

SQL help.# Database - 数据库

h*r2001-01-29 08:01

1 楼

I have some thing confused here,
I created two tables:
create table ford (yr NUMBER, mdl VARCHAR2(15));
create table gm (yr NUMBER, mdl VARCHAR(15));
then insert several values into tables,
insert into gm values( 1999, 'grand am');
insert into gm values( 2000, 'GMC');
insert into ford values (2000, 'mustang');
insert into ford values (1999, 'escort');
then I do:

select * from ford, gm;
the output is correct although duplicated,
then I do:

delete ford;

D*N2001-01-29 08:01

2 楼

When you select * from ford,gm the default join is inner join,
meaning nothing will be selected if you don't have matching
rows from ford to gm. In the first case ford is already deleted
so the inner join won't return anything.
The second case you have YR in both tables so it has to be
prefixed, like, select ford.YR from ford,gm...
But your table structures are duplicated such that a join
between these two tables don't necessarily mean anything.
Try putting all the YR and MDL records in the same

【在 h****r 的大作中提到】

: I have some thing confused here,
: I created two tables:
: create table ford (yr NUMBER, mdl VARCHAR2(15));
: create table gm (yr NUMBER, mdl VARCHAR(15));
: then insert several values into tables,
: insert into gm values( 1999, 'grand am');
: insert into gm values( 2000, 'GMC');
: insert into ford values (2000, 'mustang');
: insert into ford values (1999, 'escort');
: then I do:

h*r2001-01-29 08:01

3 楼

Thanks a lot for your point.
BTW, I have another funny question, say I have several
tables and
they include a same coloum name and I want to use the column
name
to choose some records from different tables and remove them
at
once by a nested SQL sentence, how can I do it?
for example,
table A defines as (mId Number, mName Varchar2(10));
A has records:
mId mName
-------------
101, 'Tom'
102, 'Henry'
112, 'Joe'
table B defines as (mId Number, mTitle Va

【在 D****N 的大作中提到】

: When you select * from ford,gm the default join is inner join,
: meaning nothing will be selected if you don't have matching
: rows from ford to gm. In the first case ford is already deleted
: so the inner join won't return anything.
: The second case you have YR in both tables so it has to be
: prefixed, like, select ford.YR from ford,gm...
: But your table structures are duplicated such that a join
: between these two tables don't necessarily mean anything.
: Try putting all the YR and MDL records in the same

h*r2001-01-29 08:01

4 楼

Comments:
for a common situation, the number of table may not be only
three,
and it could be picked up from the set got by using SQL
sentence:
"select * from tab".

s*e2001-01-29 08:01

5 楼

DELETE FROM (select * from tab)
WHERE mld ....;

【在 h****r 的大作中提到】

: Comments:
: for a common situation, the number of table may not be only
: three,
: and it could be picked up from the set got by using SQL
: sentence:
: "select * from tab".