Master! How Do I Delete Records in Multiple Tables with a SQL Statement? Extra points can be awarded

It is recommended that you write a stored procedure, and I will write one for your reference! cardno is a custom data type! It's easy to make mistakes with triggers.

create procedure delfixusercardno cardno

asdeclare @carno carnoselect @carno=vehicle number from fixcardindextable

where card number = @cardno

delete from allcardindextablewhere card number=@cardno

delete from fixcardindextablewhere card number=@cardno

delete from fixcardenddatetablewhere card number = @cardno

delete from cardpasswordtablewhere card number=@cardno

delete from usercarinfotablewhere vehicle plate number = @carno

What it does: Delete a fixed user record and delete the corresponding records from the AllCardIndexTable, FixCardIndexTable, FixCardEndDateTable, CardPasswordTable, and UserCarInfoTable tables.

It's better to write honestly here, and see if there is a need to write stored procedures.

set rs="")

sql="select as id from a,b,c where and and employeeid=1"

sql,conn,1,1

if not then

id=rs("id")

delete from a where employeeid="& id

delete from b where employeeid="& id

delete from c where employeeid="& id

end if

It's impossible to write a function with a SQL or loop through the recordset of A and delete B, C

Finally delete A's.

It is recommended to write a trigger, such as deleting content in A and deleting content in B and C at the same time.

It's best not to use this way, including cascading deletion, triggers but not limited to the automatic processing function of the database, the consequence of this is that the data is automatically changed in the background, in my opinion, the uncontrollable situation of the data is an extremely bad problem, and such a situation is a serious consequence of the confusion of the program** writing, I think, the database is just used to store the data, all the logic should be irrelevant to the database, the ideal state of the database should be that all the data is independent, All logic should be external circumstances.

Wrong thinking! It's obviously two SQL execution statements!

If you want to leave the first sentence wrong and the first sentence is not executed, you can set autocommit to false

Then use rollback

You can also set up cascade deletion in a diagram without triggers.

Delete t3 where column in

Select B.

from t2

inner join t1 on column = column.

delete t2

where column in

Select A.

from t1

Indicates that two SQL statements can be executed at one time by adding a semicolon to the middle. You can click "New Query" at the bottom of the SQL Server menu to run the above statement in a new window that pops up. A common SQL statement cannot be used to delete two tables.

This has to do with the intrinsic mechanics of SQL Server.

It is not possible for a single statement to delete records in multiple tables.

jseven jy's method, copy union place, change it to union all on it.

That is: the select field.

duzhi1, field4 from source tabledao ) union all(select field2 as field1, field4 from source table) union all

select field3 as field1, field4 from source table) because of the use of union, the duplicate will be merged.

Union All does not merge duplicates.

(CopySelect field.)

bai1, field4 from source table) union(select field du2 as field1, field 4 from source table) union

select field3 as field1, field4 from source table) my side test is right... You try the dao again...

Hope it helps you ...

select departid,name,distinct count(dbbh) as the number of supervised transactions,distinct count(fkbh) as the number of feedbacks.

from department left join dubanon =

left join fankuion

First of all, your table should be designed reasonably, and every modification of an item must be recorded, so that it is not very simple to query the rate of decline and change.

select *, rate of change = (a.)Goods-(select goods from student table where id=goods from student table a.)

select

max(a-table.) id)-max (Table B. id) asczfroma table, b table.

If 0 is returned, the maximum value of the two tables is the same. Otherwise, it's greater than 0 or less than 0, and you know what to do.

Use distinct to find out a record to be saved, and then delete the line with the same ticket number but not the one to save;

Or distinct to find all the records you want, and then insert a new table.

4 rows become 1 row? If you use sqlserver, you can use a very weird method: selectdistinct','+sale_itemfromsale_reportforxmlpath(''The landlord can test it and finally use a forxmlpath(''Just fine.

The result of my SQL execution above is: ,c,a,b

How did the landlord do it, I'm having a similar problem now!

select top 20 * from table name where condition and id not in (select top 10 id from table name where condition).

It's a little less efficient, but it's basically a function to be implemented, and Oracle has a way to implement SQL Server, which is relatively simple.

The method of creating a temporary table is no more efficient than this for stored procedures to utilize cursors.

I don't know what kind of yo you want.,Your ID should have sorting features in it.,Write a generic one anyway! Some functions will have sorting, and if you are afraid that the result will not be what you want after sorting, you will use the oldest method.

I built a t test table in the database with an id field in it, more than 20 rows.

create function sum10()

returns table

as return

select top 10 *

from t_test go

create function sum20()

returns table

as return

select top 20 *

from t_test go

select *

from sum20()

where id not in(select id

from sum10())

This is a bit inconvenient.,Because there's no variable used in top.,In that case.,No matter how much you take from it.,But I tried.,No.,I have to study it well.,Actually, you can do it without a function.,It should be okay to use select inlays.,You can look at the help.,Check the top or something.。

First, determine whether the number of records in the table is greater than 20.

Then, using the cursor, take the result set @@count>10 and @@count<21.