Insert row inside sql table based on its column value

Question

i am having table file which contains multiple columns , one of them is val which was combination of 3 attributes version,name and availability all three are combined with ; and stored inside val . Now i have to insert 3 rows with coping all data and value i.e

id    file_id   file_version_id   val                  type

162   190234       259           1.2;DESC;AVAIL        desc
============================================================

id   file_id   file_version_id      val                type
162   190234       259             1.2                version

id   file_id   file_version_id      val                type
162   190234       259             DESC                name

 id   file_id   file_version_id     val                 type
162   190234       259              AVAIL           availability

As you can see from the example , i have to insert 3 records with its data from parent row. The order of data is version,name and availability in case for some records name is not available i.e 3.4;NOT_AVAIL then have to insert only two records with type Version and availability

If there are only 2 values like: xxx;yyy or 1 value zzz, how do you know which is which? If they are version,name or availability? — forpas
– forpas, Commented Jun 7, 2020 at 17:23
@forpas as i mentioned order is same version,name and availability if there is two consecutive string i.e name and availabilty if first one number other one string i.e version and name. — Raushan Setthi
– Raushan Setthi, Commented Jun 7, 2020 at 17:29
Which database do you really use? PL/SQL tag suggests Oracle, but - there are MySQL and PostgreSQL, so ...? — Littlefoot
– Littlefoot, Commented Jun 7, 2020 at 17:34

forpas · Accepted Answer · 2020-06-07 17:59:43Z

1

You can use UNNEST() to split the column val and a CASE expression to update the column type:

INSERT INTO tablename (id, file_id, file_version_id, val, type)
WITH cte AS (
  SELECT *, UNNEST(STRING_TO_ARRAY(val, ';')) str
  FROM tablename                              
)
SELECT id, file_id, file_version_id, str,
  CASE
    WHEN str ~ '^[0-9\.]+$' THEN 'version'
    WHEN str IN ('AVAIL', 'NOT AVAIL') THEN 'availability'
    ELSE 'name'                   
  END
FROM cte;

See a simplified demo.

answered Jun 7, 2020 at 17:59

forpas

165k10 gold badges51 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2020-06-09 02:01:14Z

0

Presumably, it is the positions of the values in the string that determine the type, not their values. I would suggest unnest() with the position and a case expression:

insert into file (id, file_id, file_version_id, val, type)
    select f.id, f.file_id, f.file_version_id, u.val,
           (case u.n 
                when 1 then 'version'
                when 2 then 'name'
                when 3 then 'availability'
            end)
    from file f cross join lateral
         unnest(string_to_array(f.val, ';')) with ordinality u(val, n);

If you want to be sure that all values are present, you might want where f.val like '%;%;%'.

Here is a db<>fiddle.

edited Jun 9, 2020 at 2:01

answered Jun 7, 2020 at 20:58

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

2 Comments

Gordon Linoff Over a year ago

What is the error? The unnest() is fine: dbfiddle.uk/….

Raushan Setthi Over a year ago

db-fiddle.com/f/ed9DaPwaRZvwYJenjoT9rE/4 run this sql

Collectives™ on Stack Overflow

Insert row inside sql table based on its column value

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related