Mysql query to Delete records after select..? [duplicate]
1
This question already has an answer here:
MySQL Error 1093 - Can't specify target table for update in FROM clause
14 answers
after selecting records i want to delete that records..i tried following query - (after removing ,MAX(value) but its showing- You can't specify target table 'test' for update in FROM clause
DELETE FROM test WHERE ext_no IN
(
SELECT ext_no, MAX(value)
FROM test
GROUP BY ext_no
HAVING COUNT(*) > 2 AND
COUNT(*) = COUNT(CASE value WHEN 0 THEN 1 END)
);
mysql select sql-delete
asked Nov 19 '18 at 7:48
user10146017user10146017
376
marked as duplicate by Barmar mysql
Users with the mysql badge can single-handedly close mysql questions as duplicates and reopen them as needed.
StackExchange.ready(function() {
if (StackExchange.options.isMobile) return;
$('.dupe-hammer-message-hover:not(.hover-bound)').each(function() {
var $hover = $(this).addClass('hover-bound'),
$msg = $hover.siblings('.dupe-hammer-message');
$hover.hover(
function() {
$hover.showInfoMessage('', {
messageElement: $msg.clone().show(),
transient: false,
position: { my: 'bottom left', at: 'top center', offsetTop: -7 },
dismissable: false,
relativeToBody: true
});
},
function() {
StackExchange.helpers.removeMessages();
}
);
});
});
Nov 19 '18 at 8:12
This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.
add a comment |
1
This question already has an answer here:
MySQL Error 1093 - Can't specify target table for update in FROM clause
14 answers
after selecting records i want to delete that records..i tried following query - (after removing ,MAX(value) but its showing- You can't specify target table 'test' for update in FROM clause
DELETE FROM test WHERE ext_no IN
(
SELECT ext_no, MAX(value)
FROM test
GROUP BY ext_no
HAVING COUNT(*) > 2 AND
COUNT(*) = COUNT(CASE value WHEN 0 THEN 1 END)
);
mysql select sql-delete
asked Nov 19 '18 at 7:48
user10146017user10146017
376
marked as duplicate by Barmar mysql
Users with the mysql badge can single-handedly close mysql questions as duplicates and reopen them as needed.
StackExchange.ready(function() {
if (StackExchange.options.isMobile) return;
$('.dupe-hammer-message-hover:not(.hover-bound)').each(function() {
var $hover = $(this).addClass('hover-bound'),
$msg = $hover.siblings('.dupe-hammer-message');
$hover.hover(
function() {
$hover.showInfoMessage('', {
messageElement: $msg.clone().show(),
transient: false,
position: { my: 'bottom left', at: 'top center', offsetTop: -7 },
dismissable: false,
relativeToBody: true
});
},
function() {
StackExchange.helpers.removeMessages();
}
);
});
});
Nov 19 '18 at 8:12
This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.
I don't get the logic - you appear to want to delete all ext_nos where there are more than 2 records for that ext_no AND ALL records for that ext_no have a value of 0. These 2 conditions seem incompatible can you add sample data and expected outcome as text to the question please.
– P.Salmon
Nov 19 '18 at 8:07
@P.Salmon yes sure.. you can refer my previous question.. for sample data here
– user10146017
Nov 19 '18 at 8:12
add a comment |
1
1
1
This question already has an answer here:
MySQL Error 1093 - Can't specify target table for update in FROM clause
14 answers
after selecting records i want to delete that records..i tried following query - (after removing ,MAX(value) but its showing- You can't specify target table 'test' for update in FROM clause
DELETE FROM test WHERE ext_no IN
(
SELECT ext_no, MAX(value)
FROM test
GROUP BY ext_no
HAVING COUNT(*) > 2 AND
COUNT(*) = COUNT(CASE value WHEN 0 THEN 1 END)
);
mysql select sql-delete
asked Nov 19 '18 at 7:48
user10146017user10146017
376
This question already has an answer here:
MySQL Error 1093 - Can't specify target table for update in FROM clause
14 answers
after selecting records i want to delete that records..i tried following query - (after removing ,MAX(value) but its showing- You can't specify target table 'test' for update in FROM clause
DELETE FROM test WHERE ext_no IN
(
SELECT ext_no, MAX(value)
FROM test
GROUP BY ext_no
HAVING COUNT(*) > 2 AND
COUNT(*) = COUNT(CASE value WHEN 0 THEN 1 END)
);
This question already has an answer here:
MySQL Error 1093 - Can't specify target table for update in FROM clause
14 answers
mysql select sql-delete
mysql select sql-delete
asked Nov 19 '18 at 7:48
user10146017user10146017
376
asked Nov 19 '18 at 7:48
user10146017user10146017
376
asked Nov 19 '18 at 7:48
user10146017user10146017
376
asked Nov 19 '18 at 7:48
user10146017user10146017
376
asked Nov 19 '18 at 7:48
user10146017user10146017
376
376
marked as duplicate by Barmar mysql
Users with the mysql badge can single-handedly close mysql questions as duplicates and reopen them as needed.
StackExchange.ready(function() {
if (StackExchange.options.isMobile) return;
$('.dupe-hammer-message-hover:not(.hover-bound)').each(function() {
var $hover = $(this).addClass('hover-bound'),
$msg = $hover.siblings('.dupe-hammer-message');
$hover.hover(
function() {
$hover.showInfoMessage('', {
messageElement: $msg.clone().show(),
transient: false,
position: { my: 'bottom left', at: 'top center', offsetTop: -7 },
dismissable: false,
relativeToBody: true
});
},
function() {
StackExchange.helpers.removeMessages();
}
);
});
});
Nov 19 '18 at 8:12
This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.
marked as duplicate by Barmar mysql
Users with the mysql badge can single-handedly close mysql questions as duplicates and reopen them as needed.
StackExchange.ready(function() {
if (StackExchange.options.isMobile) return;
$('.dupe-hammer-message-hover:not(.hover-bound)').each(function() {
var $hover = $(this).addClass('hover-bound'),
$msg = $hover.siblings('.dupe-hammer-message');
$hover.hover(
function() {
$hover.showInfoMessage('', {
messageElement: $msg.clone().show(),
transient: false,
position: { my: 'bottom left', at: 'top center', offsetTop: -7 },
dismissable: false,
relativeToBody: true
});
},
function() {
StackExchange.helpers.removeMessages();
}
);
});
});
Nov 19 '18 at 8:12
This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.
I don't get the logic - you appear to want to delete all ext_nos where there are more than 2 records for that ext_no AND ALL records for that ext_no have a value of 0. These 2 conditions seem incompatible can you add sample data and expected outcome as text to the question please.
– P.Salmon
Nov 19 '18 at 8:07
@P.Salmon yes sure.. you can refer my previous question.. for sample data here
– user10146017
Nov 19 '18 at 8:12
add a comment |
I don't get the logic - you appear to want to delete all ext_nos where there are more than 2 records for that ext_no AND ALL records for that ext_no have a value of 0. These 2 conditions seem incompatible can you add sample data and expected outcome as text to the question please.
– P.Salmon
Nov 19 '18 at 8:07
@P.Salmon yes sure.. you can refer my previous question.. for sample data here
– user10146017
Nov 19 '18 at 8:12
I don't get the logic - you appear to want to delete all ext_nos where there are more than 2 records for that ext_no AND ALL records for that ext_no have a value of 0. These 2 conditions seem incompatible can you add sample data and expected outcome as text to the question please.
– P.Salmon
Nov 19 '18 at 8:07
I don't get the logic - you appear to want to delete all ext_nos where there are more than 2 records for that ext_no AND ALL records for that ext_no have a value of 0. These 2 conditions seem incompatible can you add sample data and expected outcome as text to the question please.
– P.Salmon
Nov 19 '18 at 8:07
@P.Salmon yes sure.. you can refer my previous question.. for sample data here
– user10146017
Nov 19 '18 at 8:12
@P.Salmon yes sure.. you can refer my previous question.. for sample data here
– user10146017
Nov 19 '18 at 8:12
add a comment |
1 Answer
1
active
oldest
votes
1
You can try below - you need to use subquery for inner select statement
DELETE FROM test WHERE ext_no IN
(
select ext_no from
(
SELECT ext_no, MAX(value)
FROM test
GROUP BY ext_no
HAVING COUNT(*) > 2 AND
COUNT(*) = COUNT(CASE value WHEN 0 THEN 1 END)
)t)
answered Nov 19 '18 at 8:07
fa06fa06
12.3k2917
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
1
You can try below - you need to use subquery for inner select statement
DELETE FROM test WHERE ext_no IN
(
select ext_no from
(
SELECT ext_no, MAX(value)
FROM test
GROUP BY ext_no
HAVING COUNT(*) > 2 AND
COUNT(*) = COUNT(CASE value WHEN 0 THEN 1 END)
)t)
answered Nov 19 '18 at 8:07
fa06fa06
12.3k2917
add a comment |
1
You can try below - you need to use subquery for inner select statement
DELETE FROM test WHERE ext_no IN
(
select ext_no from
(
SELECT ext_no, MAX(value)
FROM test
GROUP BY ext_no
HAVING COUNT(*) > 2 AND
COUNT(*) = COUNT(CASE value WHEN 0 THEN 1 END)
)t)
answered Nov 19 '18 at 8:07
fa06fa06
12.3k2917
add a comment |
1
1
1
You can try below - you need to use subquery for inner select statement
DELETE FROM test WHERE ext_no IN
(
select ext_no from
(
SELECT ext_no, MAX(value)
FROM test
GROUP BY ext_no
HAVING COUNT(*) > 2 AND
COUNT(*) = COUNT(CASE value WHEN 0 THEN 1 END)
)t)
answered Nov 19 '18 at 8:07
fa06fa06
12.3k2917
You can try below - you need to use subquery for inner select statement
DELETE FROM test WHERE ext_no IN
(
select ext_no from
(
SELECT ext_no, MAX(value)
FROM test
GROUP BY ext_no
HAVING COUNT(*) > 2 AND
COUNT(*) = COUNT(CASE value WHEN 0 THEN 1 END)
)t)
answered Nov 19 '18 at 8:07
fa06fa06
12.3k2917
answered Nov 19 '18 at 8:07
fa06fa06
12.3k2917
answered Nov 19 '18 at 8:07
fa06fa06
12.3k2917
answered Nov 19 '18 at 8:07
fa06fa06
12.3k2917
12.3k2917
add a comment |
add a comment |
I don't get the logic - you appear to want to delete all ext_nos where there are more than 2 records for that ext_no AND ALL records for that ext_no have a value of 0. These 2 conditions seem incompatible can you add sample data and expected outcome as text to the question please.
– P.Salmon
Nov 19 '18 at 8:07
@P.Salmon yes sure.. you can refer my previous question.. for sample data here
– user10146017
Nov 19 '18 at 8:12