chobowski chobowski - 17 days ago 5
MySQL Question

Deleting record in two table in one query SQL

I have two tables

EMPGROUP_TBL
SEQID | MASTERID | BUSINESS_UNIT | DIVISION | SUB_DIVISION | CLASSIFICATION | SUB_CLASSIFICATION


and

EMP_MASTERTBL
MASTERID | EMPNO | LASTNAME | FIRSTNAME | JOBTITLE | LOCATION |


In my table in ASP.NET JOBTITLE, BUSINESS_UNIT, DIVISION, SUB_DIVISION, CLASSIFICATION and SUB_CLASSIFICATION are together.

So when I have to delete the record in girdview I only need to delete the JOBTITLE in EMP_MASTERTBL and all the record in EMPGROUP_TBL.

This is my code

DELETE em.JOBTITLE, eg.BUSINESS_UNIT, eg.DIVISION, eg.SUB_DIVISION, eg.CLASIFFICATION, eg.SUB_CLASSIFICATION
FROM EMP_MASTERTBL AS em, EMPGROUP_TBL AS eg
WHERE em.MASTERID = eg.MASTERID AND eg.MASTERID = '76196'


Everytime I run or execute my code there's an error which is
Incorrect syntax near ','.


I tried different approach for my code but it's still the same.

I also tried this

DELETE JOBTITLE FROM EMP_MASTERTBL WHERE MASTERID = '76196';
DELETE FROM EMPGROUP_TBL WHERE MASTERID = '76196'


But I got an
Invalid object name 'JOBTITLE'.
error.

What could be the problem? Thanks.

Answer

When you DELETE FROM a table you are not deleting from a particular field, are you are deleting the entire row. That is why when you write the delete statement you don't include field names.

It is also possible to set up a foreign key relationship between your two tables so that when you delete from EMP_MASTERTBL it also deletes the child records in EMPGROUP_TB. http://www.mysqltutorial.org/mysql-on-delete-cascade/

Edit

On closer reading of your updated question, I think what you would really like to do is this:

UPDATE EMP_MASTERTBL
   SET JOBTITLE = NULL
   WHERE MASTERID = '76196';

DELETE FROM EMPGROUP_TB
   WHERE MASTERID = '76196';

That is, you're setting the JOBTITLE to NULL, and deleting all related records in EMPGROUP_TB.