Delete Query Problem 4

[Blorf]

Whats wrong with this?

DELETE PR1.* FROM PR1 INNER JOIN CC ON PR1.CompanyCode = CC.CompanyCode;

I just want to delete from Pr1 all records with a match on CopanyCode within CC.Companycode.

I get cant delete error.

Thanks,
ChaZ

There Are 10 Types Of People In The world:
Those That Understand BINARY And Those That Don’t.

 

[PHV]

And this ?
DELETE FROM PR1 WHERE CompanyCode In (SELECT CompanyCode FROM CC)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Golom]

Try this

DELETE * FROM PR1 

Where PR1.CompanyCode IN (Select PR1.CompanyCode 
                          From PR1 INNER JOIN CC 
                          ON PR1.CompanyCode = CC.CompanyCode)

 

[Blorf]

PHV, Golom, both work, and thank you for the quick reply.

What difference is made with the In vs inner join? One works, and the other doesn't, I know that much to be sure, but I don't fully understand why.

Again, thank both of you very much.

ChaZ

There Are 10 Types Of People In The world:
Those That Understand BINARY And Those That Don’t.

 

[abaldwin]

The inner join represents a query returning data from 2 or more tables. Delete * would mean to delete all records and SQL does not understand what to do with the fields from the second table (in effect your where statment). The solution posed works because the * represents all the fields from the target table and the Inner join used in the where is not actually visible to the delete statement except to be used as a restriction. You will find the same problem with an insert statement using joins for the same reason.

Andy

Andy Baldwin

"Testing is the most overlooked programming language on the books!

 

[Blorf]

Excellent.

This makes sense.

ChaZ

There Are 10 Types Of People In The world:
Those That Understand BINARY And Those That Don’t.