{"id":46,"date":"2015-05-30T06:56:48","date_gmt":"2015-05-30T06:56:48","guid":{"rendered":"http:\/\/www.josejavierfm.es\/blog\/?p=46"},"modified":"2015-06-19T06:57:11","modified_gmt":"2015-06-19T06:57:11","slug":"borrar-registros-mysql-a-partir-de-una-select-en-la-misma-tabla","status":"publish","type":"post","link":"https:\/\/www.josejavierfm.es\/blog\/2015\/05\/30\/borrar-registros-mysql-a-partir-de-una-select-en-la-misma-tabla\/","title":{"rendered":"borrar registros mysql a partir de una select en la misma tabla"},"content":{"rendered":"

Si intentamos borrar registros de una tabla a partir de una select<\/p>\n

delete from customers where customers.customerNumber in
\n(
\nselect customers.customerNumber
\nfrom customers
\nleft join payments on customers.customerNumber=payments.customerNumber
\ngroup by customers.customerNumber
\nhaving count(payments.amount)=0
\n)<\/p>\n

obtenemos el error:
\n[Err] 1093<\/strong> – You can’t specify target table ‘customers’ for update in FROM clause<\/p>\n

Existen dos maneras de borrar registros de una tabla a partir de una select de la misma tabla:<\/p>\n

usando in<\/strong><\/p>\n

delete from customers where customers.customerNumber in
\n(select customerNumber from (
\nselect customers.customerNumber
\nfrom customers
\nleft join payments on customers.customerNumber=payments.customerNumber
\ngroup by customers.customerNumber
\nhaving count(payments.amount)=0
\n) as t )<\/p>\n

y con inner join<\/strong><\/p>\n

delete customers<\/em> from customers inner join
\n(select customerNumber from (
\nselect customers.customerNumber
\nfrom customers
\nleft join payments on customers.customerNumber=payments.customerNumber
\ngroup by customers.customerNumber
\nhaving count(payments.amount)=0
\n) as t ) tt on customers.customerNumber=tt.customerNumber<\/p>\n

es importante decir que tabla borras, pues al hacer el join la sentencia no sabria cual borrar.<\/em><\/p>\n

 <\/p>\n\n

Twittear<\/a><\/div>\n","protected":false},"excerpt":{"rendered":"

Si intentamos borrar registros de una tabla a partir de una select delete from customers where customers.customerNumber in ( select customers.customerNumber from customers left join payments on customers.customerNumber=payments.customerNumber group by customers.customerNumber having count(payments.amount)=0 ) obtenemos el error: [Err] 1093 – You can’t specify target table ‘customers’ for update in FROM clause Existen dos maneras de […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[7],"tags":[],"_links":{"self":[{"href":"https:\/\/www.josejavierfm.es\/blog\/wp-json\/wp\/v2\/posts\/46"}],"collection":[{"href":"https:\/\/www.josejavierfm.es\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.josejavierfm.es\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.josejavierfm.es\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.josejavierfm.es\/blog\/wp-json\/wp\/v2\/comments?post=46"}],"version-history":[{"count":1,"href":"https:\/\/www.josejavierfm.es\/blog\/wp-json\/wp\/v2\/posts\/46\/revisions"}],"predecessor-version":[{"id":47,"href":"https:\/\/www.josejavierfm.es\/blog\/wp-json\/wp\/v2\/posts\/46\/revisions\/47"}],"wp:attachment":[{"href":"https:\/\/www.josejavierfm.es\/blog\/wp-json\/wp\/v2\/media?parent=46"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.josejavierfm.es\/blog\/wp-json\/wp\/v2\/categories?post=46"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.josejavierfm.es\/blog\/wp-json\/wp\/v2\/tags?post=46"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}