Reputation: 325
Delete from table
I have a table that initially has the following details in it
+-------+--------------+------------+-------+
| T_KEY | T_DATE | T_TEN | T_MEM |
+-------+--------------+------------+-------+
| 1 | 0000-00-00 | a | 0 |
| 2 | 0000-00-00 | b | 0 |
| 3 | 0000-00-00 | c | 0 |
| 4 | 0000-00-00 | d | 0 |
| 5 | 0000-00-00 | e | 0 |
| 6 | 0000-00-00 | f | 0 |
+-------+--------------+------------+-------+
After insert, the table looks as below
+-------+--------------+-----------+--------+
| T_KEY | T_DATE | T_TEN | T_MEM |
+-------+--------------+-----------+--------+
| 1 | 0000-00-00 | a | 0 |
| 2 | 0000-00-00 | b | 0 |
| 3 | 0000-00-00 | c | 0 |
| 4 | 0000-00-00 | d | 0 |
| 5 | 0000-00-00 | e | 0 |
| 6 | 0000-00-00 | f | 0 |
| 7 | 2015-01-01 | a | 1 |
| 8 | 2015-01-01 | b | 3 |
| 9 | 2015-01-01 | c | 5 |
| 10 | 2015-01-02 | a | 5 |
| 11 | 2015-01-02 | b | 8 |
| 12 | 2015-01-02 | c | 1 |
+-------+--------------+-----------+--------+
During the insert operation, for T_TEN=a,b,c non-zero values have been inserted for T_DATE & T_MEM fields. After the insert operation, I'd like to run a cleanup operation(delete query) which should identify all the T_TEN that have a row of data each for
T_DATE='0000-00-00' and T_MEM = 0
AND
T_DATE="Any value other than '0000-00-00'"
AND
T_MEM = "Any value other than zero"
Once, identified, it should delete the row that contains T_DATE='0000-00-00' and T_MEM = 0 for that particular T_TEN.
In the above example, after the insert for say T_TEN=a, the updated table has 3 entries for 'a' which are
+-------+--------------+-----------+--------+
| T_KEY | T_DATE | T_TEN | T_MEM |
+-------+--------------+-----------+--------+
| 1 | 0000-00-00 | a | 0 |
| 7 | 2015-01-02 | a | 1 |
| 10 | 2015-01-02 | a | 5 |
I'd like to get rid of the row with T_KEY=1 as T_TEN=a, now has both zero & non-zero values for the fields T_DATE & T_MEM.
Similarly, for T_TEN=b & c, I'd like to remove the rows with T_DATE='0000-00-00' and T_MEM = 0 as they have got a non-zero entry for these fields following the insert.
REQUIRED OUTPUT
+-------+-------------+-------------+-------+
| T_KEY | T_DATE | T_TEN | T_MEM |
+-------+-------------+-------------+-------+
| 4 | 0000-00-00 | d | 0 |
| 5 | 0000-00-00 | e | 0 |
| 6 | 0000-00-00 | f | 0 |
| 7 | 2015-01-01 | a | 1 |
| 8 | 2015-01-01 | b | 3 |
| 9 | 2015-01-01 | c | 5 |
| 10 | 2015-01-02 | a | 5 |
| 11 | 2015-01-02 | b | 8 |
| 12 | 2015-01-02 | c | 1 |
+-------+-------------+-------------+-------+
INSERT SQL I AM USING IS
INSERT INTO
databse_2.th_sc(T_DATE,T_TEN,T_MEM)
SELECT
ms1.M_DATE,
ms1.M_TEN,
CASE
WHEN SUM(ms1.MEM_US) BETWEEN 1 AND 3 THEN 1
WHEN SUM(ms1.MEM_US) BETWEEN 3 AND 5 THEN 3
WHEN SUM(ms1.MEM_US) BETWEEN 5 AND 10 THEN 5
WHEN SUM(ms1.MEM_US) BETWEEN 20 AND 30 THEN 8
WHEN SUM(ms1.MEM_US) BETWEEN 30 AND 50 THEN 10
WHEN SUM(ms1.MEM_US) > 50 THEN 15
ELSE 0
END AS MEM_TI
FROM
database_1.MEM_SW ms1
WHERE
ms1.MEM_IN IN (
SELECT
tw2.T_NAME
FROM
database_1.T_WY tw2
)
AND ms1.M_DATE between '2015-05-01' and '2015-06-01'
AND ms1.MEM_IN LIKE 'ks%ny%'
GROUP BY
ms1.M_DATE,
ms1.MEM_TEN;
Could someone help me on how to achieve this please?
Upvotes: 1
Views: 62
Answers (2)
Reputation: 325
The trick to getting over the error "Sorry for the wrong update earlier. I get the below error when I run the SQL - MySQL said: Documentation #1093 - You can't specify target table 'mytable' for update in FROM clause – "
is to wrap the inner SELECT with a SELECT. (http://dasunhegoda.com/1093-you-cant-specify-target-table-table_name-for-update-in-from-clause/104/)
DELETE
FROM
mytable
WHERE
t_date = '0000-00-00'
AND t_ten IN (
SELECT * FROM (
SELECT
t_ten
FROM
mytable
WHERE
NOT (t_date = '0000-00-00')
) AS XY
);
Upvotes: 0
Reputation: 94859
Delete from the table where t_date and t_mem are empty but a record exists with the same t_ten where t_date and t_mem are not both empty. Use EXISTS or IN for this:
delete
from mytable
where t_date = '0000-00-00' and t_mem = 0
and t_ten in
(
select t_ten
from mytable
where not (t_date = '0000-00-00' and t_mem = 0)
);
Upvotes: 1