CODEWITHSUNDEEP
sqloracle-database
mhd
mhd

Reputation: 1351

INSERT INTO / SELECT DISTINCT results in primary key violation for a SYS_GUID

I had some problems bulk inserting data from another table into a new one. The target looked somewhat like this:

CREATE TABLE TEST_T (
      T_GUID RAW(16) DEFAULT SYS_GUID() NOT NULL,
      T_VAL1 NUMBER(10) NOT NULL,
      T_VAL2 VARCHAR2(10) NOT NULL,
      PRIMARY KEY (T_GUID)
)

The simplified version of the statement I intended to fill it with data:

INSERT INTO TEST_T (T_VAL1, T_VAL2)
SELECT DISTINCT
     CAST(SUBSTR(zip_code, 1,1) AS NUMBER) as  t_val1,
     zip_code as t_val2
FROM OTHER_TABLE_T
WHERE ...
ORDER BY t_val1

As I'm not providing a T_GUID valued, I would've assumed that I get one provided by the SYS_GUID function for each new line. But something is going wrong, and I get a uniqueness constraint violation for the primary key.

If I remove the DISTINCT, the statement succeeds, but I get lots of duplicate entries. And, of course, if I explicitly provide a SYS_GUID() call in my SELECT, that has exactly the same result.

Now I found that if I simply put another SELECT around mine, it works out alright, no constraint violations and the distinct rows get inserted:

INSERT INTO ...
SELECT x.* FROM (
    SELECT DISTINCT ...
) x

So where do the duplicate guids come from? If the full set of rows doesn't have problems, why would removing rows via distinct cause trouble? As SYS_GUID creates a unique identifier for each call, I could only imagine that in the distinct case it only gets called once for the whole clause, which is solved by the surrounding wrapping SELECT. I'd be very happy if someone could explain how the execution differs in that case.

Upvotes: 5

Views: 8380

Answers (2)

Art
Art

Reputation: 5792

Replace CAST with to_number() and add guid. Test your select first, then insert...:

SELECT DISTINCT
    sys_guid() guid 
    To_Number(SUBSTR(zip_code, 1,1)) as  t_val1,
    zip_code as t_val2
 FROM OTHER_TABLE_T
/

Distinct GUID example:

SELECT sys_guid() gid, deptno
  FROM
 (
 SELECT distinct deptno FROM scott.emp
 )
ORDER BY deptno
/

GID                                 DEPTNO
-------------------------------------------
DC9B9132492C1A45E04011AC3EEB463A    10
DC9B9132492B1A45E04011AC3EEB463A    20
DC9B9132492A1A45E04011AC3EEB463A    30

Upvotes: 0

GriffeyDog
GriffeyDog

Reputation: 8376

Try something like this:

insert into test_t(t_guid, t_val1, t_val2)
  select sys_guid(), t_val1, t_val2
    from (select distinct to_number(substr(zip_code, 1, 1)) as t_val1, zip_code as t_val2
            from other_table_t
           where ...)

Upvotes: 1

Related Questions