MySql multiple table delete query

Question

0.00/5 (No votes)

See more:

I am trying to execute a multi table delete in on sql query but I cant seem to get it right. the query is below. any help will be deeply appreciated. The 'id' is already set from a different portion of the code.

connection = Properties.Settings.Default.cvmanagerConnectionString;

sql = "delete from apps, contacts, dept using apps join contacts, join dept where apps.FileLoc =  contacts.FileLoc AND apps.FileLoc = dept.FileLoc AND apps.FileLoc = '" + id + "'";
 try
 {
     SqlConnection conn = new SqlConnection(connection);
     SqlCommand cmd = new SqlCommand(sql, conn);
     conn.Open();
     cmd.ExecuteNonQuery();
 }
 catch (Exception ex)
 {
     MessageBox.Show(ex.ToString());
 }

Posted 12-Jan-11 3:02am

Member 7250221

Updated 12-Jan-11 3:25am

RaviRanjanKr

v2

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

dasblinkenlight · Answer 1 · 2011-01-12T03:20:00

Solution 1

Judging from the examples here, it looks like you have an extra ',' in your query. I think it should be using apps join contacts join dept where

Posted 12-Jan-11 3:20am

dasblinkenlight

Comments

[no name] 12-Jan-11 12:08pm

what about if i add more tables? would the query look like this:
using apps join contacts join dept join roles join industry where...
or
using apps join contacts, join dept, join roles, join industry where...

dasblinkenlight 12-Jan-11 12:35pm

From the syntax on the ref manual page it looks like there are no commas between the JOIN clauses. You may also want to consider moving join criteria away from the WHERE clause into the ON clauses of their joins for clarity, like this:

delete from apps, contacts, dept using apps join contacts on apps.FileLoc =  contacts.FileLoc join dept on apps.FileLoc = dept.FileLoc where apps.FileLoc = '" + id + "'"

[no name] 13-Jan-11 5:17am

it still does not work

dasblinkenlight 13-Jan-11 8:04am

Could you give more specifics on the "does not work" part? Do you get an exception, or the statement executes without deleting the rows you expected to be deleted? If there's an exception, could you provide the details of it?

tosandeep · Answer 2 · 2011-06-08T02:35:00

private void button1_Click(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection("Data Source=jcs-pc;Initial Catalog=deepak;Persist Security Info=True;User ID=sa;Password=jcs#");
SqlCommand cmd = new SqlCommand();
string s1 = "insert into deep (id,name)values(1,'sandeep')";

string s2 = "insert into deep (id,name)values(1,'sandeep')";

string s3 = "insert into sany (id,name')values(1,'sandeep')";

cmd.CommandText = s1 + s2 + s3;
cmd.Connection = con;
con.Open();
cmd.ExecuteNonQuery();
con.Close();

}

(sandeep.k115@gmail.com)