Delete rows from table with where clause from another table

Question

I already broke my head while I was thinking how it can be done. Need to make a query with a condition that compares with the id of the current table entry.

That is, something similar to:

DELETE
FROM table1
WHERE TIMESTAMPDIFF(MINUTE, Time_ADD, now()) >= (
        SELECT del_time
        FROM table2
        WHERE table1.id = table2.id
        )

A similar query produces an error indicating that the subquery issues multiple values if the table1 is not specified for the id or that the table1.id does not exist if the table1 name is specified

P.S. Sorry for my English, if I made a mistakes.

EDIT 1 For ex. CREATE Query might seen like:

CREATE TABLE table1(
    id int identity(1,1),
    some_value varchar(max),
    Time_ADD datetime
)

CREATE TABLE table2(
    id int,
    del_time int
)

In table2 I have int value in minutes. After that time id int table1 must be deleted.

pls post also the `CREATE TABLE` commands for `table1` and `table2` and add some example data too.. — Sebastian Brosch, Jun 19 '17 at 06:55

score 0 · Answer 1 · answered Jun 19 '17 at 07:05

0

this might be what you're looking for

DELETE table1
FROM table1
INNER JOIN table2 ON table1.ID = table2.ID
AND TIMESTAMPDIFF(MINUTE, Time_ADD, now()) >= table2.del_time

answered Jun 19 '17 at 07:05

Esteban P.

2,789
2
27
43

This query didnt work in phpMyAdmin. Error on `INNER JOIN` – ZeT Jun 20 '17 at 08:39
have a look here: https://stackoverflow.com/questions/652770/delete-with-join-in-mysql – Esteban P. Jun 20 '17 at 09:47

score 0 · Answer 2 · answered Jun 19 '17 at 07:51

0

DELETE FROM table1 WHERE TIMESTAMPDIFF(MINUTE, Time_ADD, now()) >=

Make sure for this query you must have to return only one row.

(SELECT del_time FROM table2 WHERE table1.id = table2.id)

Either you can filter it more.

answered Jun 19 '17 at 07:51

Shimul

87
1
3
12

query returned more than one value because phpMyAdmin is not seen link to table1: `table1.id` in subquery – ZeT Jun 20 '17 at 08:40

Delete rows from table with where clause from another table

2 Answers2