하나의 쿼리로 두 테이블에서 삭제

하나의 쿼리로 두 테이블에서 삭제

---

mysql에 두 개의 테이블이 있습니다.

#messages table  : 
messageid
messagetitle 
.
.

#usersmessages table 
usersmessageid 
messageid
userid
.
.

이제 메시지 테이블에서 삭제하고 싶습니다. 하지만 내가 messageid = '1'로 메시지를 삭제할 때 예를 들어 usersmessage에 여전히 존재합니다.이 두 테이블에서 한 번에 삭제해야합니다.

그래서 다음 쿼리를 사용합니다.

DELTE FROM messages LEFT JOIN usersmessages USING(messageid) WHERE messageid='1' ; 

그럼 나는 테스트

   DELETE FROM messages , usersmessages 
   WHERE messages.messageid = usersmessages.messageid 
   and messageid='1' ; 

그러나이 두 질의는이 임무를 만족시키지 않습니다.

---

세미콜론으로 구분할 수 없습니까?

Delete from messages where messageid = '1';
Delete from usersmessages where messageid = '1'

또는

INNER JOIN아래와 같이 사용하십시오.

DELETE messages , usersmessages  FROM messages  INNER JOIN usersmessages  
WHERE messages.messageid= usersmessages.messageid and messages.messageid = '1'

---

DELETE a.*, b.* 
FROM messages a 
LEFT JOIN usersmessages b 
ON b.messageid = a.messageid 
WHERE a.messageid = 1

번역 : 테이블에서 삭제 된 메시지 테이블 경우의 MessageID = 1, uersmessages가 의 MessageID =의 MessageID 테이블의이 메시지 의 해당 행 삭제 uersmessages의 테이블을.

---

당신이 중 하나를 만들어야 FOREIGN KEY과 ON DELETE CASCADE:

ALTER TABLE usersmessages
ADD CONSTRAINT fk_usermessages_messageid
FOREIGN KEY (messageid)
REFERENCES messages (messageid)
ON DELETE CASCADE

, 또는 트랜잭션에서 두 개의 쿼리를 사용하여 수행하십시오.

START TRANSACTION;;

DELETE
FROM    usermessages
WHERE   messageid = 1

DELETE
FROM    messages
WHERE   messageid = 1;

COMMIT;

Transaction affects only InnoDB tables, though.

---

You have two options:

First, do two statements inside a transaction:

BEGIN;
  DELETE FROM messages WHERE messageid = 1;
  DELETE FROM usermessages WHERE messageid = 1;
COMMIT;

Or, you could have ON DELETE CASCADE set up with a foreign key. This is the better approach.

CREATE TABLE parent (
  id INT NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE child (
  id INT, parent_id INT,
  FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE CASCADE
);

You can read more about ON DELETE CASCADE here.

---

DELETE message.*, usersmessage.* from users, usersmessage WHERE message.messageid=usersmessage.messageid AND message.messageid='1'

---

no need for JOINS:

DELETE m, um FROM messages m, usersmessages um

WHERE m.messageid = 1 

AND m.messageid = um.messageid 

---

Try this please

DELETE FROM messages,usersmessages

USING messages

INNER JOIN usermessages on (messages.messageid = usersmessages.messageid)

WHERE messages.messsageid='1'

---

Try this..

DELETE a.*, b.*  
FROM table1 as a, table2 as b  
WHERE a.id=[Your value here] and b.id=[Your value here]

I let id as a sample column.

Glad this helps. :)

---

You can also use like this, to delete particular value when both the columns having 2 or many of same column name.

DELETE project , create_test  FROM project INNER JOIN create_test
WHERE project.project_name='Trail' and  create_test.project_name ='Trail' and project.uid= create_test.uid = '1';

---

DELETE t1 , t2 FROM table1 t1 LEFT JOIN table2 t2 ON t1.id = t2.id WHERE t1.id = some_id

참고URL : https://stackoverflow.com/questions/1233451/delete-from-two-tables-in-one-query