Trying to delete a row with left join

Question

But I am getting a syntax error:

Warning: PDOStatement::execute(): SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LEFT JOIN projects ON projects_members.project_members_main_project = proj' at line 2 in

DELETE FROM projects_members
LEFT JOIN projects
    ON projects_members.project_members_main_project = projects.project_id
WHERE projects_members.project_members_id = ?
AND projects.project_page = ?

This is my tables:

CREATE TABLE IF NOT EXISTS `projects_members` (
  `project_members_id` int(11) NOT NULL,
  `project_members_main_project` int(11) NOT NULL,
  `project_members_member` int(11) NOT NULL,
  `project_members_date` datetime DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `projects_members`
--

INSERT INTO `projects_members` (`project_members_id`, `project_members_main_project`, `project_members_member`, `project_members_date`) VALUES
(10, 1, 3, '2016-01-13 17:54:35');


CREATE TABLE IF NOT EXISTS `projects` (
  `project_id` int(11) NOT NULL,
  `project_name` varchar(100) NOT NULL,
  `project_page` int(11) NOT NULL,
  `project_page_type` varchar(15) NOT NULL,
  `project_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `project_startdate` datetime NOT NULL,
  `project_occupation` varchar(100) NOT NULL,
  `project_description` text NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;

    --
    -- Dumping data for table `projects`
    --

    INSERT INTO `projects` (`project_id`, `project_name`, `project_page`, `project_page_type`, `project_date`, `project_startdate`, `project_occupation`, `project_description`) VALUES
    (1, 'kk', 1, 'company', '2015-11-18 14:50:45', '0000-00-00 00:00:00', 'bb', 'kkk'),
    (2, 'lol', 1, 'company', '2015-11-23 17:46:47', '2015-11-30 00:00:00', 'lol', 'fffff'),
    (3, 'lol', 1, 'company', '2015-11-23 17:48:07', '2015-11-30 00:00:00', 'lol', 'fffff'),
    (4, 'lol', 1, 'company', '2015-11-23 17:48:40', '2015-11-30 00:00:00', 'lol', 'fffff'),
    (5, 'lol', 1, 'company', '2015-11-23 17:49:35', '2015-11-30 00:00:00', 'lol', 'fffff'),
    (6, 'lol', 1, 'company', '2015-11-23 17:49:57', '2015-11-30 00:00:00', 'lol', 'fffff'),
    (7, 'lol', 1, 'company', '2015-11-23 17:51:06', '2015-11-30 00:00:00', 'lol', 'fffff'),
    (8, 'lol', 1, 'company', '2015-11-23 17:57:12', '2015-10-28 00:00:00', 'lol', 'oool');

`DELETE FROM` that's invalid syntax. Here, see this answer http://stackoverflow.com/a/2763245/1415724 — Funk Forty Niner, Jan 13 '16 at 18:20

kojow7 · Accepted Answer · 2016-01-13T18:21:33.973

You need to specify which tables to delete from.

To delete from the project_members table only:

DELETE project_members FROM projects_members
LEFT JOIN projects
    ON projects_members.project_members_main_project = projects.project_id
WHERE projects_members.project_members_id = ?
AND projects.project_page = ?

or to delete from the projects table only:

DELETE projects FROM projects_members
LEFT JOIN projects
    ON projects_members.project_members_main_project = projects.project_id
WHERE projects_members.project_members_id = ?
AND projects.project_page = ?

or to delete from both tables:

DELETE projects, project_members FROM projects_members
LEFT JOIN projects
    ON projects_members.project_members_main_project = projects.project_id
WHERE projects_members.project_members_id = ?
AND projects.project_page = ?

Trying to delete a row with left join

1 Answers1