如何使用1个以上的表删除sql中的记录 [英] how can i delete a record in sql using more than 1 table

问题描述

A.O.A


即时通讯在sql中有问题，谁能帮助我
我有3张桌子
调动，过帐详细信息和员工

我必须从子位置表中删除子位置
但是首先我必须检查是否有任何员工正在postingdetail表中对该子分区进行工作，如果是，则不应删除该记录.

请回复

A.O.A


im having a problem in sql, can anyone help me
i have 3 tables
sublocation, postingdetail and employee

i have to delete sublocid from sublocation table
but first i have to check that is there any employee working on that sublocation from postingdetail table, if yes then the record shouldn''t be deleted.

plz reply

table sublocation
------------
 sublocid
 sublocname
 mainlocid

table postingdetail
============
 empid
 subloc
 mainloc

table employee
==========
 empid
 empname
 sublocid
 mainlocid

推荐答案

表:子位置
===================

table : Sublocation
====================

   columnName        DataType        Length    Allow Nulls
PK	SubLocID	    numeric               9       	0
	SubLocName	varchar	             50    	1
FK	MainLocID	   numeric                9     	1
	ShrtDesc	    varchar	            50    	1

表格:过帐明细
===================

table : Posting Detail
====================

   columnName        DataType        Length    Allow Nulls
PK	TID	          numeric	        9			0
FK	LocID	        numeric	        9			1
FK	SubLocId		numeric				9			1
	DesigId			numeric	        9			1
	RegPosting		numeric	        9			1
	Posted	        numeric			9			1

表:员工
===================

table : Employee
====================

   columnName        DataType     Length    Allow Nulls
PK	TID                numeric        9             0
FK	EMPID             varchar     	 50	        1
	EmpName		  varchar        250            1
	EmpCodePrefix     varchar         50            1
	EmpNo             varchar         50            0
FK	DeptID            numeric            9           1
FK	MainLocID         numeric           9            1
FK	SubLocID	numeric	         9	1

如果postingdetail.subloc是子地点，则使用此查询从任何员工将无法工作的子地点中删除子地点

If the postingdetail.subloc is the sublocid then use this query to delete sublocation from sublocation where any employee will not working

DELETE FROM sublocation WHERE sublocid NOT IN (
	SELECT sublocid FROM employee
	WHERE sublocid NOT IN ( SELECT subloc FROM postingdetail) 
)

您可以使用以下存储过程删除子位置:

You can delete your sublocations by using a stored procedure like this :

create procedure sp_DeleteSubLoc
@sublocid int
as
begin
  if ((select count(*) from [posting detail] where sublocid = @sublocid) = 0)
  begin
     delete sublocation where sublocid = @sublocid
     return 0
  end
  else
  begin
     return 1
  end
end

您可以通过这种方式使用它:

you can use it by this way :

DECLARE	@return_value int

EXEC	@return_value = sp_DeleteSubLoc 15

SELECT	'Return Value' = @return_value

15是@sublocid的示例，如果成功，则返回0，否则返回1.

希望对您有所帮助.

15 is an example for @sublocid, if it succeeds returns 0 otherwise returns 1.

Hope it helps.

这篇关于如何使用1个以上的表删除sql中的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！