{"id":271,"date":"2011-03-29T18:06:24","date_gmt":"2011-03-30T01:06:24","guid":{"rendered":"https:\/\/gonzalo.aro.cl\/blog\/?p=271"},"modified":"2011-03-30T03:53:59","modified_gmt":"2011-03-30T10:53:59","slug":"rownum-select-union-e-insert-into","status":"publish","type":"post","link":"https:\/\/gonzalo.aro.cl\/blog\/2011\/03\/29\/rownum-select-union-e-insert-into\/","title":{"rendered":"ROWNUM, select union e insert into"},"content":{"rendered":"<p>Un problema que me sucedio radic\u00f3 en que requer\u00eda hacer desde una vista ya existente un INSERT INTO hacia una tabla destino, esta vista ten\u00eda una columna que hac\u00eda de identificador, pero al ver la consulta me di cuenta que utilizaba un UNION, era m\u00e1s o menos as\u00ed:<\/p>\n<pre class=\"brush: sql\">\r\ninsert into tabla (id, col1)\r\nselect rownum id, xyz from t1\r\nunion\r\nselect rounum id, zyx from t2\r\n<\/pre>\n<p>El problema no era la consulta ya que esta arrojaba resultado, la problematica se encontraba que el id generado no era unico, ya que en ambos casos el rownum part\u00eda de 1, lo que provocaba un error en el insert into.<\/p>\n<p>La soluci\u00f3n fue, crear un trigger que incrementara una secuencia y el valor lo depositara en ID, <\/p>\n<pre class=\"brush: sql\">\r\ncreate or replace trigger TRG_tabla \r\nbefore insert on tabla\r\n   for each row \r\nbegin  \r\n   if inserting then \r\n      if :NEW.ID is null then \r\n         select SQ_tabla.nextval into :NEW.ID from dual;\r\n      end if\r\n   end if\r\nend;\r\n<\/pre>\n<p>eliminar del insret into la columna ID, y luego ejecutar el insert into, con esto se soluci\u00f3n el error de clave duplicada.<\/p>\n<pre class=\"brush: sql\">\r\ninsert into tabla (col1)\r\nselect xyz from t1\r\nunion\r\nselect  zyx from t2\r\n<\/pre>\n<p> <del datetime=\"2011-03-30T11:52:55+00:00\"><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Un problema que me sucedio radic\u00f3 en que requer\u00eda hacer desde una vista ya existente un INSERT INTO hacia una tabla destino, esta vista ten\u00eda una columna que hac\u00eda de identificador, pero al ver la consulta me di cuenta que utilizaba un UNION, era m\u00e1s o menos as\u00ed: insert into tabla (id, col1) select rownum [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3,4,9],"tags":[],"class_list":["post-271","post","type-post","status-publish","format-standard","hentry","category-base-de-datos","category-desarrollo","category-oracle"],"_links":{"self":[{"href":"https:\/\/gonzalo.aro.cl\/blog\/wp-json\/wp\/v2\/posts\/271"}],"collection":[{"href":"https:\/\/gonzalo.aro.cl\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/gonzalo.aro.cl\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/gonzalo.aro.cl\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/gonzalo.aro.cl\/blog\/wp-json\/wp\/v2\/comments?post=271"}],"version-history":[{"count":5,"href":"https:\/\/gonzalo.aro.cl\/blog\/wp-json\/wp\/v2\/posts\/271\/revisions"}],"predecessor-version":[{"id":273,"href":"https:\/\/gonzalo.aro.cl\/blog\/wp-json\/wp\/v2\/posts\/271\/revisions\/273"}],"wp:attachment":[{"href":"https:\/\/gonzalo.aro.cl\/blog\/wp-json\/wp\/v2\/media?parent=271"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/gonzalo.aro.cl\/blog\/wp-json\/wp\/v2\/categories?post=271"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/gonzalo.aro.cl\/blog\/wp-json\/wp\/v2\/tags?post=271"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}