Mysql – Deleting rows with MySQL LEFT JOIN

delete-rowleft-joinMySQLsql-delete

I have two tables, one for job deadlines, one for describe a job. Each job can take a status and some statuses means the jobs' deadlines must be deleted from the other table.

I can easily SELECT the jobs/deadlines that meets my criteria with a LEFT JOIN:

SELECT * FROM `deadline`
LEFT JOIN `job` ON deadline.job_id = job.job_id
WHERE `status` = 'szamlazva'
OR `status` = 'szamlazhato'
OR `status` = 'fizetve'
OR `status` = 'szallitva'
OR `status` = 'storno'

(status belongs to job table not deadline)

But when I'd like to delete these rows from deadline, MySQL throws an error. My query is:

DELETE FROM `deadline`
LEFT JOIN `job`
ON deadline.job_id = job.job_id
WHERE `status` = 'szamlazva'
OR `status` = 'szamlazhato'
OR `status` = 'fizetve'
OR `status` = 'szallitva'
OR `status` = 'storno'

MySQL error says nothing:

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 job ON deadline.job_id = job.job_id WHERE status = 'szaml' at line 1

How can I turn my SELECT into a working DELETE query?

Best Answer

You simply need to specify on which tables to apply the DELETE.

Delete only the deadline rows:

DELETE `deadline` FROM `deadline` LEFT JOIN `job` ....

Delete the deadline and job rows:

DELETE `deadline`, `job` FROM `deadline` LEFT JOIN `job` ....

Delete only the job rows:

DELETE `job` FROM `deadline` LEFT JOIN `job` ....

Related Solutions

Mysql – concatenate multiple MySQL rows into one field

You can use GROUP_CONCAT:

SELECT person_id,
   GROUP_CONCAT(hobbies SEPARATOR ', ')
FROM peoples_hobbies
GROUP BY person_id;

As Ludwig stated in his comment, you can add the DISTINCT operator to avoid duplicates:

SELECT person_id,
   GROUP_CONCAT(DISTINCT hobbies SEPARATOR ', ')
FROM peoples_hobbies
GROUP BY person_id;

As Jan stated in their comment, you can also sort the values before imploding it using ORDER BY:

SELECT person_id, 
       GROUP_CONCAT(hobbies ORDER BY hobbies ASC SEPARATOR ', ')
FROM peoples_hobbies
GROUP BY person_id;

As Dag stated in his comment, there is a 1024 byte limit on the result. To solve this, run this query before your query:

SET group_concat_max_len = 2048;

Of course, you can change 2048 according to your needs. To calculate and assign the value:

SET group_concat_max_len = CAST(
                     (SELECT SUM(LENGTH(hobbies)) + COUNT(*) * LENGTH(', ')
                           FROM peoples_hobbies
                           GROUP BY person_id) AS UNSIGNED);

Sql-server – LEFT JOIN vs. LEFT OUTER JOIN in SQL Server

As per the documentation: FROM (Transact-SQL):

<join_type> ::= 
    [ { INNER | { { LEFT | RIGHT | FULL } [ OUTER ] } } [ <join_hint> ] ]
    JOIN

The keyword OUTER is marked as optional (enclosed in square brackets). In this specific case, whether you specify OUTER or not makes no difference. Note that while the other elements of the join clause is also marked as optional, leaving them out will make a difference.

For instance, the entire type-part of the JOIN clause is optional, in which case the default is INNER if you just specify JOIN. In other words, this is legal:

SELECT *
FROM A JOIN B ON A.X = B.Y

Here's a list of equivalent syntaxes:

A LEFT JOIN B            A LEFT OUTER JOIN B
A RIGHT JOIN B           A RIGHT OUTER JOIN B
A FULL JOIN B            A FULL OUTER JOIN B
A INNER JOIN B           A JOIN B

Also take a look at the answer I left on this other SO question: SQL left join vs multiple tables on FROM line?.

enter image description here

Best Answer

Related Solutions

Mysql – concatenate multiple MySQL rows into one field

Sql-server – LEFT JOIN vs. LEFT OUTER JOIN in SQL Server

Related Topic