Need Sp fro data delete

• manju3606

  SSC Journeyman

  Points: 81

  More actions

  November 18, 2011 at 1:16 am

  #250773

  Hi,

  I am using below query to delete rows from table A which rows are available in table B also (same rows in two table).

  So now what i want is i have to specify a column name in where condition but i dont know the column names of table A and table B then

  how could i specify a column names in where condition ?

  with cte as (select * From ces_test INTERSECT select * from ces_testimport)

  DELETE ces from ces_test ces where exists (select 1 from cte where cte.Name = ces.Name) .

  Please help me........

  Thanks

• November 18, 2011 at 1:33 am

  This was removed by the editor as SPAM

• Devendrakumar Shirbad

  SSC-Forever

  Points: 42493

  More actions

  November 18, 2011 at 1:57 am

  #1409727

  Stewart "Arturius" Campbell (11/18/2011)

  ---

  To find column names, try

  SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'MyTable'

  OR

  EXEC sp_help 'MyTable' -- It will return more than what you want.

• sami.sqldba

  Ten Centuries

  Points: 1248

  More actions

  November 18, 2011 at 9:10 am

  #1410008

  Hi Bro

  I really not getting what you want. if you want to know the names of the columns that are present in each of the tables then you can go with the above sql statments given also can try for

  select the table name and press Alt+F1 u will get details of that particular table along with its column.

  But if you want the query to delete the data from tableA whose reocrds are present in tableB with some where condition then check out both the tables column which have a matching id then put those column in where condition.

  Thanks & Regards
  Syed Sami Ur Rehman
  SQL-Server (Developer)
  Hyderabad
  Email-sami.sqldba@gmail.com

• Sam Bendayan

  SSC-Addicted

  Points: 479

  More actions

  November 21, 2011 at 9:32 pm

  #1411191

  I believe all you need is a delete with an EXISTS:

  delete a

  from tableA a

  where exists (select 't'

  from tableB b

  where a.Name= b.Name)

  Let me know if that works.

  SB