Incorrect syntax near the word left

I am getting incorrect syntax error near the word left. Any ideas? I have a Transaction and Account table, so when a Account is deleted (PK AccountID), i want to delete and rows in the Transaction table with FK AccountID that is the same.

Thanks.

       public int DeleteAccount(Account account)
    {
        //var sqlCmd = "DELETE FROM Account LEFT JOIN [Transaction]ON Account.AccountID = Transaction.AccountID WHERE AccountId = " + account.AccountID;

        //var sqlCmd = "delete from [Account] from [Account] left join [Transaction] WHERE Account.AccountID = " + account.AccountID + " AND Transaction.AccountID = " + account.AccountID;
        var sqlCmd = "delete from [Account] WHERE Account.AccountID = " + account.AccountID + " left join [Transaction] ON Account.AccountID = Transaction.AccountID";
        SqlCommand cmd = new SqlCommand(sqlCmd, cnn1);
        var rowAff = cmd.ExecuteNonQuery();
        return rowAff;

3 answers

  • answered 2020-03-31 11:56 Phoenix Stoneham

    You need to do Delete from [Account] from [Account] left join ...

    From what I understand the first "from" tells the query which table in the results to delete the rows from, while the second "from" is for the query, normally we only have one table in the query so we don't need to specify the first from.

  • answered 2020-03-31 11:58 Gordon Linoff

    There are many ways that this is just bad:

    delete
        from [Account] left join
             [Transaction]
             WHERE AccountID = " + account.AccountID;
    
    1. It does not specify WHAT table you want to delete from.
    2. You are munging the query string with a constant, instead of using parameters.
    3. You have no ON clause with a LEFT JOIN.
    4. AccountId is referenced with no table qualifier.

    You should delete this question and ask a new question. Explain what you want to do. Provide sample data, desired results, and an appropriate database tag.

  • answered 2020-03-31 12:11 M Dutt

    Exactly what you want to do is not clear.

    The LEFT JOIN keyword returns all records from the left table (table1), and the matched records from the right table (table2). The result is NULL from the right side if there is no match.

    Assuming you want to delete record in table A, where no matching records in table B

    DELETE A FROM TABLE1 A
    LEFT JOIN TABLE2 B
    ON A.ID = B.SOMEID
    AND A.SOMECOL = H.COL
    WHERE B.CASEID IS NULL