newbie question (SQL)

[Balint Toth]

Hi all,

I have got two tables (table1, table2), each of them has primary key ID
filed (bigint), and the second one has a foregin key, what points to the
first table ID field (so the table2 has a table1_id column).

I would like to delete the rows from table1 which aren't refeered from
table2. Unfortunatelly I cannot figure out the SQL statement. Please help
me!

And how can I implement it in C#?

Thanks in advance!

Best regards,
Bálint Tóth

 

[Andy Gaskell]

The SQL would look like this:
DELETE FROM Table1 WHERE ID NOT IN (SELECT table1_id FROM Table2)

I'll assume you're using SQL Server - here's the C# code:

string mySelectQuery = "DELETE FROM Table1 WHERE ID NOT IN (SELECT
table1_id FROM Table2)";
SqlConnection myConnection = new SqlConnection(<your connection string
here>);
SqlCommand myCommand = new SqlCommand(mySelectQuery, myConnection);
myCommand.ExecuteNonQuery();

 

[William Ryan eMVP]

Hi Balint:

If you have a PK/FK relationship, you can't have any rows in Table 2 that
aren't in table 1.

If you want to do it on the server side.... DELETE From Table1 Where
ID_Column NOT IN (SELECT ID_Column from Table2)

Either way though, if the relationship you stated is in place is there, you
can't have any orphans. If I misunderstood yoru problem, please let me
know.

Bill


www.devbuzz.com
www.knowdotnet.com