CODEWITHSUNDEEP
sqloracle11g
Даниил Алексашин
Даниил Алексашин

Reputation: 5

How to insert values which are not present in the table?

I have a table with rows which are look like this:

| ID | NAME | LOCALE |
| x  | name |   en   |
| x  | name |   ru   |
| y  | name1|   en   |
| y  | name1|   ru   |

And so on. But some rows are present in just one locale. I need to insert missing rows, so for every ID and NAME there is 2 rows for 2 locales.

Upvotes: 0

Views: 56

Answers (3)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520908

Assuming that each name would only ever have two locales present, then here is a straightforward option:

INSERT INTO yourTable (ID, NAME, LOCALE)
SELECT
    ID,
    NAME,
    CASE WHEN LOCALE = 'en' THEN 'ru' ELSE 'en' END
FROM
(
    SELECT ID, NAME, MAX(LOCALE) AS LOCALE
    FROM yourTable
    GROUP BY ID, NAME
    HAVING COUNT(*) = 1
) t;

If you actually have more than two locales, then I think we would have to assume that there is some table containing all locales. The query for that case would be more complicated than what I wrote above.

Upvotes: 2

schlonzo
schlonzo

Reputation: 1406

Add missing name entries:

INSERT INTO <YourTable>
(ID, NAME, LOCALE)
(
    SELECT t1. ID, 'name', t1.LOCALE 
    FROM <YourTable> t1
    WHERE NOT EXISTS
    (
        SELECT t2.LOCALE 
        FROM <YourTable> t2 
        WHERE t2.NAME = 'name' AND t1.LOCALE = t2.LOCALE
    )
)

Add missing name1 entries:

INSERT INTO <YourTable>
(ID, NAME, LOCALE)
(
    SELECT t1.ID, 'name1', t1.LOCALE 
    FROM <YourTable> t1
    WHERE NOT EXISTS
    (
        SELECT t2.LOCALE 
        FROM <YourTable> t2 
        WHERE t2.NAME = 'name1' AND t1.LOCALE = t2.LOCALE
    )
)

If the strings name and name1 don't play a role, and you just need a second row for any locale whioch exist only once you can use:

INSERT INTO <YourTable>
(ID, NAME, LOCALE)
(
    SELECT t1.ID, 'Placeholder for locale', t1.LOCALE 
    FROM <YourTable> t1
    WHERE 
    (
        SELECT COUNT(*) 
        FROM <YourTable> t2 
        WHERE t1.LOCALE = t2.LOCALE
    ) = 1
)

Upvotes: 0

Aleksej
Aleksej

Reputation: 22949

If I understand well, you may need something like the following:

test case:

create table someTable(ID, NAME, LOCALE) as (
    select 'x', 'name'  ,'en' from dual union all
    select 'x', 'name'  ,'ru' from dual union all
    select 'y', 'name1' ,'en' from dual union all
    select 'y', 'name1' ,'ru' from dual union all
    select 'z', 'ZZZZ' ,'ru' from dual
)

add missing rows:

merge into someTable s
using(
        select *
        from 
            (select 'en' LOCALE from dual union
             select 'ru' LOCALE from dual
            )  
            cross join
            ( select distinct ID, name from someTable)
     ) x
on (x.id = s.id and x.name = s.name and s.locale = x.locale)
when not matched then
    insert values (x.id, x.name, x.locale)

The result:

ID NAME  LOCALE
-- ----- ------
x  name  en    
x  name  ru    
y  name1 en    
y  name1 ru    
z  ZZZZ  ru    
z  ZZZZ  en

Upvotes: 0

Related Questions