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.
| Author |
Topic |
|
Pinto
Aged Yak Warrior
590 Posts |
 Posted - 2005-11-24 : 05:38:05
|
| I have two tables and they have a common field BookingDateRef (string)which can be used to link them. In Table1 I want to delete all the records that have a corresponding record in Table 2. I cannot use Cascade delete as BookingDateRef is not a key field. I also want to do this in a stored procedure. Can someone help me please. |
|
|
chiragkhabaria
Master Smack Fu Yak Hacker
1907 Posts |
Posted - 2005-11-24 : 05:55:00
|
| Well.. you can do the same in the trigger also .. or else its the simple query .. you can use that Delete From Table1 Where <Key Column> in (Select <Linking Column > From TAble2)hope this work for u ..Sucess Comes to those who Believe in Beauty of their Dream.. |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-11-24 : 05:55:08
|
| Make sure you have Backup and run thisDelete T1 from Table1 T1 inner join Table2 on T1.BookingDateRef = T2.BookingDateRefMadhivananFailing to plan is Planning to fail |
|
|
|
chiragkhabaria
Master Smack Fu Yak Hacker
1907 Posts |
Posted - 2005-11-24 : 06:02:50
|
| Nice Query .. Never knewed that for delete also you can specify joins.. Learnt somthing new.. :-)Sucess Comes to those who Believe in Beauty of their Dream.. |
|
|
|
Pinto
Aged Yak Warrior
590 Posts |
Posted - 2005-11-24 : 06:23:45
|
| I am getting an error 'The column prefix 'tblRB_BlockBookingDates' does not match with a table name or alias name used in the query.' - but all the names are correctDelete BB_BookingDateRef from tblRB_BlockBookingDates BB_BookingDateRef inner join tblRB_ExceptionsBlockBooking on tblRB_BlockBookingDates.BB_BookingDateRef = tblRB_ExceptionsBlockBooking.EX_BookingDateRef |
|
|
|
Pinto
Aged Yak Warrior
590 Posts |
Posted - 2005-11-24 : 06:32:20
|
| Actually, chiragkhabaria, yours worked ! I got an error with madhivanan's code.... |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2005-11-24 : 07:25:20
|
| " I got an error with madhivanan's code...."...because he left out the alias "T2" after naming Table2...small mistake on his part....the principle was outlined well! |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-11-24 : 07:38:38
|
Yes. I missed that Alias name  >>Never knewed that for delete also you can specify joins.. Learnt somthing new.. :-)Yes It is possible as that of UpdateMadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
Delete records in table