quote:

---

Originally posted by ssg14j

I have 2 tables emp, emp1 with the fields id, name, age in both the tables.

I need to delete the id which is common in both the tables emp,emp1.

i tried the query "delete emp,emp1 from emp,emp1 where emp.id=emp1.id and emp.id='xx'"
I got syntax error.

I tried using join also to delete the data from 2 tables @ a time.

Please help me to get the correct query..


Thanx,
S.S.G

---

quote:

---

Originally posted by haroon2k9

quote:

---

Originally posted by ssg14j

I have 2 tables emp, emp1 with the fields id, name, age in both the tables.

I need to delete the id which is common in both the tables emp,emp1.

i tried the query "delete emp,emp1 from emp,emp1 where emp.id=emp1.id and emp.id='xx'"
I got syntax error.

I tried using join also to delete the data from 2 tables @ a time.

Please help me to get the correct query..


Thanx,
S.S.G

---

delete from
(
select emp.*,emp1.* from emp inner join emp2 on emp.id=emp1.id
)as t

---

quote:

---

Originally posted by ssg14j

I have 2 tables emp, emp1 with the fields id, name, age in both the tables.

I need to delete the id which is common in both the tables emp,emp1.

i tried the query "delete emp,emp1 from emp,emp1 where emp.id=emp1.id and emp.id='xx'"
I got syntax error.

I tried using join also to delete the data from 2 tables @ a time.

Please help me to get the correct query..


Thanx,
S.S.G

---

quote:

---

Originally posted by madhivanan

quote:

---

Originally posted by ssg14j

I have 2 tables emp, emp1 with the fields id, name, age in both the tables.

I need to delete the id which is common in both the tables emp,emp1.

i tried the query "delete emp,emp1 from emp,emp1 where emp.id=emp1.id and emp.id='xx'"
I got syntax error.

I tried using join also to delete the data from 2 tables @ a time.

Please help me to get the correct query..


Thanx,
S.S.G

---

Do you want to delete data from both the tables?



Madhivanan

Failing to plan is Planning to fail

---

quote:

---

Originally posted by senthil_nagore

quote:

---

Originally posted by haroon2k9

quote:

---

Originally posted by ssg14j

I have 2 tables emp, emp1 with the fields id, name, age in both the tables.

I need to delete the id which is common in both the tables emp,emp1.

i tried the query "delete emp,emp1 from emp,emp1 where emp.id=emp1.id and emp.id='xx'"
I got syntax error.

I tried using join also to delete the data from 2 tables @ a time.

Please help me to get the correct query..


Thanx,
S.S.G

---

delete from
(
select emp.*,emp1.* from emp inner join emp2 on emp.id=emp1.id
)as t

---

Just Correcting a type O

delete from
(
select emp.*,emp2.* from emp inner join emp1 on emp.id=emp1.id
)as t

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

---

quote:

---

Originally posted by haroon2k9

quote:

---

Originally posted by madhivanan

quote:

---

Originally posted by ssg14j

I have 2 tables emp, emp1 with the fields id, name, age in both the tables.

I need to delete the id which is common in both the tables emp,emp1.

i tried the query "delete emp,emp1 from emp,emp1 where emp.id=emp1.id and emp.id='xx'"
I got syntax error.

I tried using join also to delete the data from 2 tables @ a time.

Please help me to get the correct query..


Thanx,
S.S.G

---

Do you want to delete data from both the tables?



Madhivanan

Failing to plan is Planning to fail

---

please see the hightlighted in red color,what OP tried to do ..please correct me if iam wrong(Kind Request)madhi.

---

quote:

---

Originally posted by madhivanan

quote:

---

Originally posted by haroon2k9

quote:

---

Originally posted by madhivanan

quote:

---

Originally posted by ssg14j

I have 2 tables emp, emp1 with the fields id, name, age in both the tables.

I need to delete the id which is common in both the tables emp,emp1.

i tried the query "delete emp,emp1 from emp,emp1 where emp.id=emp1.id and emp.id='xx'"
I got syntax error.

I tried using join also to delete the data from 2 tables @ a time.

Please help me to get the correct query..


Thanx,
S.S.G

---

Do you want to delete data from both the tables?



Madhivanan

Failing to plan is Planning to fail

---

please see the hightlighted in red color,what OP tried to do ..please correct me if iam wrong(Kind Request)madhi.

---

Ok. It should be


declare @t table(id int)
insert into @t
select e.id from emp as e inner join emp1 as e1 on e.id=e1.id

delete from emp where id in (select id from @t)
delete from emp1 where id in (select id from @t)


Madhivanan

Failing to plan is Planning to fail

---

quote:

---

Originally posted by haroon2k9

quote:

---

Originally posted by madhivanan

quote:

---

Originally posted by haroon2k9

quote:

---

Originally posted by madhivanan

quote:

---

Originally posted by ssg14j

I have 2 tables emp, emp1 with the fields id, name, age in both the tables.

I need to delete the id which is common in both the tables emp,emp1.

i tried the query "delete emp,emp1 from emp,emp1 where emp.id=emp1.id and emp.id='xx'"
I got syntax error.

I tried using join also to delete the data from 2 tables @ a time.

Please help me to get the correct query..


Thanx,
S.S.G

---

Do you want to delete data from both the tables?



Madhivanan

Failing to plan is Planning to fail

---

please see the hightlighted in red color,what OP tried to do ..please correct me if iam wrong(Kind Request)madhi.

---

Ok. It should be


declare @t table(id int)
insert into @t
select e.id from emp as e inner join emp1 as e1 on e.id=e1.id

delete from emp where id in (select id from @t)
delete from emp1 where id in (select id from @t)


Madhivanan

Failing to plan is Planning to fail

---

Hi madhi,what about this..doesn't this do it?Hope you have valid reason not use of this..or you have shown other method of doing it?.
could you please explain ?
delete from
(
select emp.*,emp1.* from emp inner join emp1 on emp.id=emp1.id
)as t

---

quote:

---

That will not work
You can't delete a derived table which is derived from more than one table

---