Skip to Main Content

insert into table with restrictions

user11301181Apr 1 2014 — edited Apr 1 2014

Hi,

I'm trying insert into table rows but I've problems. I explain the tables and the problem.

I have old table with duplicates with this structure and values:

CREATE TABLE controls (

ID               VARCHAR(12) NOT NULL,

fdate            VARCHAR(10) NOT NULL,

fhour              VARCHAR(5) NOT NULL,

place             VARCHAR(50) NOT NULL,

code       NUMBER(4),

date_code VARCHAR(10),

matter         VARCHAR(14),

answer        VARCHAR(20)

);

 

the values are similar as these:


IOI7999O30422008-03-1511:00Sotoseca12008-03-17sacarosadopaje leve
IOI7999O30422008-03-1523:56La Granja de la Alameda12008-03-17sacarosadopaje leve
OII2056O84762008-03-1419:08Sotoseca12008-03-17dormir poco
QQO8904O76672008-03-1711:10Sotocalores12008-03-20manzanilladopaje grave
QIQ0237Q10902008-03-1913:40Villasequillo del Canto Rodao12008-03-20escaquearse
QOI1274Q76552008-03-2114:34Sierra del Bosque12008-03-21mostodopaje leve

QOQ0821O32492008-04-0510:22Valpiedras de los Trigos22008-04-07fructosadopaje leve
IQI6926O49492008-04-0809:05San Pedro del Lago22008-04-09mieldopaje leve
OQQ8999O84932008-04-0716:54Estacas de los Castillos22008-04-10naranjadopaje grave
IQI5315O73792008-04-1311:39Sotosombras de la Arboleda22008-04-14escaquearse


The first table that I insert is:


CREATE TABLE register

(

  fdate VARCHAR2(10) NOT NULL,

  ID VARCHAR2(12) NOT NULL,

  fhour VARCHAR(5) NOT NULL,

  place  VARCHAR2(50) NOT NULL,

  CONSTRAINT PK_REGISTER PRIMARY KEY(fdate,ID),

);

And the insert in this table is:

insert into register(fdate,ID,fhour,place)

select fdate, id, max(fhour), max(fplace) from controls group by (fdate,ID);

For example: between these rows:

IOI7999O30422008-03-1511:00La Granja de la Alameda12008-03-17sacarosadopaje leve
IOI7999O30422008-03-1523:56La Granja de la Alameda12008-03-17sacarosadopaje leve

only insert the second one because the hour is greather.

The matter is in insert on the second table.

CREATE TABLE EVIDENCES

(

  fdateVARCHAR2(10) NOT NULL,

  ID VARCHAR2(12) NOT NULL,

  matter VARCHAR2(14),

  answer VARCHAR2(20) NOT NULL,

  code NUMBER(4) NOT NULL,

  date_code VARCHAR2(10) NOT NULL,

  CONSTRAINT CK_code CHECK (code>0),

  CONSTRAINT PK_EVIDENCES PRIMARY KEY (code,  date_code),

  CONSTRAINT FK_EVIDENCES FOREIGN KEY (date, ID) REFERENCES register (date, ID)

);

the insertion instruction I use is:

insert into EVIDENCES (fdate, ID, matter, answer, code, date_code)

  select distinct fdate, ID, matter, answer, code, date_code from controls

  where answer is not null and code is not null and date_code is not null;

In the examples following:

IOI7999O30422008-03-1511:00Sotoseca12008-03-17sacarosadopaje leve
IOI7999O30422008-03-1523:56La Granja de la Alameda12008-03-17sacarosadopaje leve
OII2056O84762008-03-1419:08Sotoseca12008-03-17dormir poco

only insert  this row because the hour is the first. The other registers don't must in the evidences table.

IOI7999O30422008-03-1511:00Sotoseca12008-03-17sacarosadopaje leve

Please help with my insertion code.

Thanks in advance.

Comments
Post Details
Added on Apr 1 2014
5 comments
136 views