sybase下联合多表进行delete和update
答案:2 悬赏:80 手机版
解决时间 2021-03-21 14:12
- 提问者网友：孤凫
- 2021-03-20 19:56
sybase下联合多表进行delete和update
最佳答案
- 五星知识达人网友：末日狂欢
- 2021-03-20 20:14
在删除一个表的数据时，where条件中需要联合其他表。开始用exists做，但速度很慢。后来，用google查相关资料时，发现sybase对delete语句做了扩展，允许delete和update的时候，做多表的连接。
一、DELETE
delete的语法为：
delete [[database.]owner.]{table_name|view_name}
[from [[database.]owner.]{view_name|table_name
[(index {index_name|table_name} [prefetch size][lru|mru])]}
[,[[database.]owner.]{view_name|table_name
[(index {index_name|table_name} [prefetch size][lru|mru])]}]...]
[where search_conditions]
delete语句只删除delete关键字后from关键字前表中满足where语句中条件的记录，对于from关键字后的表，记录将不做任何改动。
举个例子，我要删除tAlarm中dOccurDate在'2005-11-1'和'2005-11-2'之间，并且在tAlarm_Month中已经有相同记录（Alarm_Id相同）的记录，则SQL语句为：
delete tAlarm from tAlarm_Month
where tAlarm.dOccurDate> '2005-11-1'
and tAlarm.dOccurDate<'2005-11-2'
and tAlarm.Alarm_Id=tAlarm_Month.Alarm_Id
二、UPDATe
update的语法为
update [[database.]owner.]{table_name|view_name}
set [[[database.]owner.]{table_name.|view_name.}]column_name1={expression1|null|(select_statement)}
|variable_name1={expression1|null|(select_statement)}
[,column_name2={expression2|null|(select_statement)}]...
|variable_name2={expression1|null|(select_statement)}
[from [database.]owner.]{table_name|view_name}[,[[database.]owner.]{table_name|view_name}]]...
[where search_conditions]
例如，我想把tPc中的Discharge_Time更新为tAlarm表中对应Alarm（Pc_Id与tPc中指定PC的Pc_Id相同）中清除Alarm的那一条的Event_Time则可以这样写：
update tPc set tPc.Discharge_Time=tAlarm.Event_Time
from tAlarm
where tPc.Perceived_Severity=5
and tAlarm.Pc_Id=tPc.Pc_Id
and tAlarm.Perceived_Severity=5
一、DELETE
delete的语法为：
delete [[database.]owner.]{table_name|view_name}
[from [[database.]owner.]{view_name|table_name
[(index {index_name|table_name} [prefetch size][lru|mru])]}
[,[[database.]owner.]{view_name|table_name
[(index {index_name|table_name} [prefetch size][lru|mru])]}]...]
[where search_conditions]
delete语句只删除delete关键字后from关键字前表中满足where语句中条件的记录，对于from关键字后的表，记录将不做任何改动。
举个例子，我要删除tAlarm中dOccurDate在'2005-11-1'和'2005-11-2'之间，并且在tAlarm_Month中已经有相同记录（Alarm_Id相同）的记录，则SQL语句为：
delete tAlarm from tAlarm_Month
where tAlarm.dOccurDate> '2005-11-1'
and tAlarm.dOccurDate<'2005-11-2'
and tAlarm.Alarm_Id=tAlarm_Month.Alarm_Id
二、UPDATe
update的语法为
update [[database.]owner.]{table_name|view_name}
set [[[database.]owner.]{table_name.|view_name.}]column_name1={expression1|null|(select_statement)}
|variable_name1={expression1|null|(select_statement)}
[,column_name2={expression2|null|(select_statement)}]...
|variable_name2={expression1|null|(select_statement)}
[from [database.]owner.]{table_name|view_name}[,[[database.]owner.]{table_name|view_name}]]...
[where search_conditions]
例如，我想把tPc中的Discharge_Time更新为tAlarm表中对应Alarm（Pc_Id与tPc中指定PC的Pc_Id相同）中清除Alarm的那一条的Event_Time则可以这样写：
update tPc set tPc.Discharge_Time=tAlarm.Event_Time
from tAlarm
where tPc.Perceived_Severity=5
and tAlarm.Pc_Id=tPc.Pc_Id
and tAlarm.Perceived_Severity=5
全部回答
- 1楼网友：玩家
- 2021-03-20 20:26
在删除一个表的数据时，where条件中需要联合其他表。开始用exists做，但速度很慢。后来，用google查相关资料时，发现sybase对delete语句做了扩展，允许delete和update的时候，做多表的连接。
一、DELETE
delete的语法为：
delete [[database.]owner.]{table_name|view_name}
[from [[database.]owner.]{view_name|table_name
[(index {index_name|table_name} [prefetch size][lru|mru])]}
[,[[database.]owner.]{view_name|table_name
[(index {index_name|table_name} [prefetch size][lru|mru])]}]...]
[where search_conditions]
delete语句只删除delete关键字后from关键字前表中满足where语句中条件的记录，对于from关键字后的表，记录将不做任何改动。
举个例子，我要删除tAlarm中dOccurDate在'2005-11-1'和'2005-11-2'之间，并且在tAlarm_Month中已经有相同记录（Alarm_Id相同）的记录，则SQL语句为：
delete tAlarm from tAlarm_Month
where tAlarm.dOccurDate> '2005-11-1'
and tAlarm.dOccurDate<'2005-11-2'
and tAlarm.Alarm_Id=tAlarm_Month.Alarm_Id
二、UPDATe
update的语法为
update [[database.]owner.]{table_name|view_name}
set [[[database.]owner.]{table_name.|view_name.}]column_name1={expression1|null|(select_statement)}
|variable_name1={expression1|null|(select_statement)}
[,column_name2={expression2|null|(select_statement)}]...
|variable_name2={expression1|null|(select_statement)}
[from [database.]owner.]{table_name|view_name}[,[[database.]owner.]{table_name|view_name}]]...
[where search_conditions]
例如，我想把tPc中的Discharge_Time更新为tAlarm表中对应Alarm（Pc_Id与tPc中指定PC的Pc_Id相同）中清除Alarm的那一条的Event_Time则可以这样写：
update tPc set tPc.Discharge_Time=tAlarm.Event_Time
from tAlarm
where tPc.Perceived_Severity=5
and tAlarm.Pc_Id=tPc.Pc_Id
and tAlarm.Perceived_Severity=5
一、DELETE
delete的语法为：
delete [[database.]owner.]{table_name|view_name}
[from [[database.]owner.]{view_name|table_name
[(index {index_name|table_name} [prefetch size][lru|mru])]}
[,[[database.]owner.]{view_name|table_name
[(index {index_name|table_name} [prefetch size][lru|mru])]}]...]
[where search_conditions]
delete语句只删除delete关键字后from关键字前表中满足where语句中条件的记录，对于from关键字后的表，记录将不做任何改动。
举个例子，我要删除tAlarm中dOccurDate在'2005-11-1'和'2005-11-2'之间，并且在tAlarm_Month中已经有相同记录（Alarm_Id相同）的记录，则SQL语句为：
delete tAlarm from tAlarm_Month
where tAlarm.dOccurDate> '2005-11-1'
and tAlarm.dOccurDate<'2005-11-2'
and tAlarm.Alarm_Id=tAlarm_Month.Alarm_Id
二、UPDATe
update的语法为
update [[database.]owner.]{table_name|view_name}
set [[[database.]owner.]{table_name.|view_name.}]column_name1={expression1|null|(select_statement)}
|variable_name1={expression1|null|(select_statement)}
[,column_name2={expression2|null|(select_statement)}]...
|variable_name2={expression1|null|(select_statement)}
[from [database.]owner.]{table_name|view_name}[,[[database.]owner.]{table_name|view_name}]]...
[where search_conditions]
例如，我想把tPc中的Discharge_Time更新为tAlarm表中对应Alarm（Pc_Id与tPc中指定PC的Pc_Id相同）中清除Alarm的那一条的Event_Time则可以这样写：
update tPc set tPc.Discharge_Time=tAlarm.Event_Time
from tAlarm
where tPc.Perceived_Severity=5
and tAlarm.Pc_Id=tPc.Pc_Id
and tAlarm.Perceived_Severity=5
我要举报
如以上回答内容为低俗、色情、不良、暴力、侵权、涉及违法等信息，可以点下面链接进行举报！
点此我要举报以上问答信息
大家都在看
推荐资讯