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 |
|
duf
Starting Member
39 Posts |
 Posted - 2012-04-11 : 04:38:18
|
| Can I use a one query to delete data from more than one table?Deletes data from several tables using these queries:"DELETE FROM IDOP WHERE _ID_OPIS='" + Indeks + "'";"DELETE FROM IDKON WHERE _ID_OPIS='" + Indeks + "'";I am trying to send query like this:"DELETE FROM IDOP, IDKON, IDZZZ, IDCN, IDWARTOSC, IDOPIS, IDNAD, IDODB, IDNETTO, NAD, ODB, IDWARSTAT WHERE _ID_OPIS='" + Indeks + "'";This method results in an error. |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-04-11 : 06:48:25
|
| You cannot delete from multiple tables that way, you have to do one at a time. |
 |
|
|
vinu.vijayan
Posting Yak Master
227 Posts |
Posted - 2012-04-12 : 06:27:01
|
| If the data you are trying to delete from the tables is related, then you can join the tables in a CTE(Common Table Expression) and delete from the CTE. This would delete the data from all the tables.Hope this helps you.Vinu VijayanN 28° 33' 11.93148"E 77° 14' 33.66384" |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-04-12 : 06:54:24
|
quote:
Originally posted by vinu.vijayan
If the data you are trying to delete from the tables is related, then you can join the tables in a CTE(Common Table Expression) and delete from the CTE. This would delete the data from all the tables.Hope this helps you.Vinu VijayanN 28° 33' 11.93148"E 77° 14' 33.66384"
I think don't this is correct. While you can delete using a CTE, if the deletion affects more than one underlying table, the operation will fail. |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2012-04-12 : 08:12:37
|
quote:
Originally posted by vinu.vijayan
If the data you are trying to delete from the tables is related, then you can join the tables in a CTE(Common Table Expression) and delete from the CTE. This would delete the data from all the tables.Hope this helps you.Vinu VijayanN 28° 33' 11.93148"E 77° 14' 33.66384"
Did you try what you say?
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
vinu.vijayan
Posting Yak Master
227 Posts |
Posted - 2012-04-12 : 08:34:03
|
| My apologies to Duf, Sunita and Webfred.I had tried it with one table.It worked then.I assumed that it would be the same incase of joins. Was bz doing somethng so couldn't verify what I had posted.Its totally my bad. Will make sure I stop assuming and trying things practically before I post.Thanx guys,Vinu VijayanN 28° 33' 11.93148"E 77° 14' 33.66384" |
|
|
|
|
|
|
|
|
Delete from more than one table with one query