problem in deleting records - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

problem in deleting records

Author

Topic

hotshot_21
Yak Posting Veteran

97 Posts

Posted - 2006-01-15 : 23:42:27

help me out on this one.
i have 2 text boxes in my page.
user enter any number in those two text boxes.
i slect that many record randomly from my main table, and put it into two another tables.
now the problem is coming in how to delete those records which were randomly selected from main table in main table.
for eg
main table contains
srNo. UswerID
1 abcd
2 trtr
3 tret
4 yghg
5 jjhj

user enters in text box1 '2' and in text box2 `1'
so total of 3 random records are selected
and put it into two another table say

table1
sr.no UserID
2 trtr

and table2 contains

sr.no. userid
3 tret
5 jjhj

now i want to delete these records which are sr.no 2,3,5 from the main table.
how do i do it as user can enter any number in the text box.so writing multiple delete statements would not be possible.
how do i write statements or help me with logic.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-15 : 23:46:44

user inner join

delete  mt
from    maintable mt inner join table1 t1
    on  mt.srno    = t1.srno

delete  mt
from    maintable mt inner join table1 t2
    on  mt.srno    = t2.srno

-----------------
'KH'

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-15 : 23:47:27

After inserting into other tables, use

Delete M
from Main M inner join table1 T1 on M.srno=T1.srno

Delete M
from Main M inner join table2 T2 on M.srno=T2.srno

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-15 : 23:51:01

You are faster khtan

Madhivanan

Failing to plan is Planning to fail

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2006-01-15 : 23:54:18

Or Try this in one query

Delete M
from Main M
inner join
( select * from Table1 union select * from Table2 )a
on M.srno = a.srno

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-15 : 23:54:32

quote:
Originally posted by madhivanan

You are faster khtan

Madhivanan

Failing to plan is Planning to fail

Got a new mouse

-----------------
'KH'

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-15 : 23:57:04

quote:
Originally posted by shallu1_gupta

Or Try this in one query

Delete M
from Main M
inner join
( select * from Table1 union select * from Table2 )a
on M.srno = a.srno

Well. You dont have to have *, just use srno

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-15 : 23:59:01

quote:
Originally posted by shallu1_gupta

Or Try this in one query

Delete M
from Main M
inner join
( select * from Table1 union select * from Table2 )a
on M.srno = a.srno

Am I right ? This might not be as efficeint. If Table1 and Table2 are huge, it will result in a union of both table first before joining with Main table.

-----------------
'KH'

hotshot_21
Yak Posting Veteran

97 Posts

Posted - 2006-01-16 : 00:03:49

Delete
from test inner join Randomtwo on test.Recordnum=Randomtwo.Recordnum

Delete
from test inner join Randomthree on test.Recordnum=Randomthree.Recordnum

hotshot_21
Yak Posting Veteran

97 Posts

Posted - 2006-01-16 : 00:04:32

Delete
from test inner join Randomtwo on test.Recordnum=Randomtwo.Recordnum

Delete
from test inner join Randomthree on test.Recordnum=Randomthree.Recordnum

i put this query and get error near "inner"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-16 : 00:07:51

[code]Delete t
from test t inner join Randomtwo on t.Recordnum=Randomtwo.Recordnum

Delete t
from test t inner join Randomthree on t.Recordnum=Randomthree.Recordnum[/code]

-----------------
'KH'

hotshot_21
Yak Posting Veteran

97 Posts

Posted - 2006-01-16 : 00:09:39

can u explain what is 't'.

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2006-01-16 : 00:21:36

quote:

Am I right ? This might not be as efficeint. If Table1 and Table2 are huge, it will result in a union of both table first before joining with Main table.

Right khtan

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-16 : 00:52:55

quote:
Originally posted by hotshot_21

can u explain what is 't'.

It is Alias Name given to the tables.
Did you you read first two replies?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources