CODEWITHSUNDEEP
mysqlsqloracle-database
WooseokChoi
WooseokChoi

Reputation: 21

How to add 'sequence'column to make a PK in table(Oracle and MySQL)

I have a question about adding a sequence column in the table I have.

The table looks like this.

-SQL(MySQL) :

SELECT A.CI_NUM, 
       A.DL_DT, 
       A.DL_PRC
FROM (SELECT '1000000001' AS CI_NUM, '20210701' AS DL_DT, 700 AS DL_PRC
      UNION ALL
      SELECT '1000000001' AS CI_NUM, '20210701' AS DL_DT, 500 AS DL_PRC
      UNION ALL 
      SELECT '1000000001' AS CI_NUM, '20210701' AS DL_DT, 600 AS DL_PRC
      UNION ALL 
      SELECT '1000000001' AS CI_NUM, '20210805' AS DL_DT, 600 AS DL_PRC
      UNION ALL 
      SELECT '1000000001' AS CI_NUM, '20210805' AS DL_DT, 350 AS DL_PRC
      UNION ALL 
      SELECT '1000000001' AS CI_NUM, '20210805' AS DL_DT, 400 AS DL_PRC
      UNION ALL 
      SELECT '2000000001' AS CI_NUM, '20210510' AS DL_DT, 300 AS DL_PRC
      UNION ALL 
      SELECT '2000000001' AS CI_NUM, '20210510' AS DL_DT, 350 AS DL_PRC
      UNION ALL 
      SELECT '2000000001' AS CI_NUM, '20210510' AS DL_DT, 200 AS DL_PRC
      UNION ALL 
      SELECT '2000000001' AS CI_NUM, '20210603' AS DL_DT, 700 AS DL_PRC
      UNION ALL 
      SELECT '2000000001' AS CI_NUM, '20210603' AS DL_DT, 650 AS DL_PRC
      UNION ALL 
      SELECT '2000000001' AS CI_NUM, '20210603' AS DL_DT, 400 AS DL_PRC
) A;

-result :

enter image description here

In this table, I want to add a sequence column to make a PK for this table.

The table that I want looks like this.

enter image description here

I want to add SQ column using ALTER TABLE and want to apply this in Oracle table and MySQL table.

If you answer this question, I really appreciate that.

Upvotes: 0

Views: 992

Answers (3)

FanoFN
FanoFN

Reputation: 7114

This is probably what you're looking for:

SELECT CI_NUM, DL_DT,
       ROW_NUMBER() OVER (PARTITION BY CI_NUM, DL_DT) AS SQ, /*generate SQ*/
       DL_PRC
  FROM ( 
      ...your subquery..
   ) A;

If you're on version that supports ROW_NUMBER() function, then this solution should suffice. But if you're on an older version, try this instead:

SELECT CI_NUM, DL_DT,
       CASE WHEN @cn = CI_NUM AND @dd = DL_DT THEN @rownum := @rownum+1
            ELSE @rownum := 1 END AS SQ,
       DL_PRC,
       @cn := CI_NUM,
       @dd := DL_DT
  FROM (SELECT @cn := NULL, @dd:= NULL, @rownum := 1) v
  CROSS JOIN ( 
      ...your subquery..
   ) A
;

Longer query, a little harder to understand but with the same idea of using ROW_NUMBER().

Demo fiddle

Upvotes: 0

Sashko Micov
Sashko Micov

Reputation: 51

Hi you can get the SQ value using window function, please see sql bellow:

SELECT A.CI_NUM, 
   A.DL_DT,
   row_number() over (partition by A.CI_NUM,A.DL_DT) AS SQ,
   A.DL_PRC
FROM (SELECT '1000000001' AS CI_NUM, '20210701' AS DL_DT, 700 AS DL_PRC
  UNION ALL
  SELECT '1000000001' AS CI_NUM, '20210701' AS DL_DT, 500 AS DL_PRC
  UNION ALL 
  SELECT '1000000001' AS CI_NUM, '20210701' AS DL_DT, 600 AS DL_PRC
  UNION ALL 
  SELECT '1000000001' AS CI_NUM, '20210805' AS DL_DT, 600 AS DL_PRC
  UNION ALL 
  SELECT '1000000001' AS CI_NUM, '20210805' AS DL_DT, 350 AS DL_PRC
  UNION ALL 
  SELECT '1000000001' AS CI_NUM, '20210805' AS DL_DT, 400 AS DL_PRC
  UNION ALL 
  SELECT '2000000001' AS CI_NUM, '20210510' AS DL_DT, 300 AS DL_PRC
  UNION ALL 
  SELECT '2000000001' AS CI_NUM, '20210510' AS DL_DT, 350 AS DL_PRC
  UNION ALL 
  SELECT '2000000001' AS CI_NUM, '20210510' AS DL_DT, 200 AS DL_PRC
  UNION ALL 
  SELECT '2000000001' AS CI_NUM, '20210603' AS DL_DT, 700 AS DL_PRC
  UNION ALL 
  SELECT '2000000001' AS CI_NUM, '20210603' AS DL_DT, 650 AS DL_PRC
  UNION ALL 
  SELECT '2000000001' AS CI_NUM, '20210603' AS DL_DT, 400 AS DL_PRC
) A;

If you have that table in your database you first need to add SQ column with some default value then use the sql above and write a cte that will be used for updating the original table, then you can add a composite PK on the table like this:

ALTER TABLE MyTble
   ADD CONSTRAINT PK_MyTable PRIMARY KEY(CI_NUM,DL_DT,SQ);

going forward you also will need trigger or somehow to get the next value in every group(CI_NUM,DL_DT) and use that as value for SQ in your insert statement.

Hope I give you an idea how to solve your problem.

Upvotes: 1

astentx
astentx

Reputation: 6751

For Oracle 12c and above you may use generated ... as identity to create an autoincrement column. Then make it a primary key, because Oracle throws an error if you do this in single operation.

create table t(val int)

insert into t values(0)

alter table t add (
  id int generated by default as identity
)

alter table t add (
  constraint t_pk primary key (id)
)

select *
from t

VAL | ID
--: | -:
  0 |  1

db<>fiddle here

For MySQL it can be done in one step, because it forces you to declare it as a pk:

create table t(val int)

insert into t values(0)

alter table t add (
  id int auto_increment primary key
)

select *
from t

val | id
--: | -:
  0 |  1

db<>fiddle here

Upvotes: 0

Related Questions