2

I am pretty sure that this is a simple matter and that it's answered but I seems like not able to find it online, or to phrase it correctly.

I have a table (ven_data) that has a few columns: id_v, lang, more data columns The unique constraint here is (id_v, lang) pair.

I need to find all ID's that have some languages missing, and then INSERT...SELECT from one of the languages to all the missing languages.

What I do now, I am fetching ALL ID's that has less then the number of languages I need:

SELECT id_v, COUNT( 1 ) as cnt
FROM  `ven_data` 
GROUP BY id_v
HAVING cnt <8
ORDER BY cnt ASC

Then I export it to Notepadd++ and remove all unneeded data, make a line of ID's like this: 1,4,5,6

And then make the INSERT...SELECT

INSERT INTO `ven_data`(`id_v`, `lang`, data columns)
SELECT `id_v`, elementInner, data columns 
FROM `ven_data` WHERE `id_v` in (1,4,5,6) and `lang` = element
        on duplicate key update lang=elementInner;

Now I have not problem iterating over the languages in the stored procedure but how do I get the result of the first SQL into the id_v in (1,4,5,6) ???

2 Answers 2

0

I'll choose to make the database generate a (virtual) table with all (id_v, lang) pairs that you are expected to have at the end.

To do so, you start by getting the list (actually set) of all DISTINCT lang:

SELECT DISTINCT
    lang
FROM
    ven_data ;

... and let's imagine it is this one

| lang |
| :--- |
| EN   |
| FR   |
| IT   |
| CA   |
| DE   |
| DK   |
| GR   |
| NL   |

We do the same to get the set of all DISTINCT id_v:

SELECT DISTINCT
    id_v
FROM
    ven_data ;
| id_v |
| ---: |
|    1 |
|    2 |
|    3 |
|    4 |
|    8 |
|    9 |
|   11 |
|   12 |
|   13 |

What should be in your ven_data table is actually the cartesian product of both sets. That is accomplished in SQL by means of a CROSS JOIN.

That results in:

id_v | lang
---: | :---
   1 | EN  
   2 | EN  
   3 | EN  
   4 | EN  
   8 | EN  
   9 | EN  
  11 | EN  
  12 | EN  
  13 | EN  
   [...]
   1 | NL  
   2 | NL  
   3 | NL  
   4 | NL  
   8 | NL  
   9 | NL  
  11 | NL  
  12 | NL  
  13 | NL  

What you actually want to do is INSERT in your table all the rows in this cartesian product that aren't already there.

You do this by means of:

INSERT INTO 
    ven_data
    (id_v, lang)
SELECT
    id_v, lang
FROM
    -- The whole set of (id_v, lang) pairs
    (SELECT DISTINCT
        lang
    FROM
        ven_data
    ) AS languages
    CROSS JOIN
    (SELECT DISTINCT
        id_v
    FROM
        ven_data
    ) AS id_list
WHERE
    -- The pair (id_v, lang) is not already in place
    NOT EXISTS
    (SELECT 
        1
    FROM 
        ven_data v2
    WHERE 
        v2.id_v = id_list.id_v AND v2.lang = languages.lang
    ) ;

Note that this WHERE condition can also be written in more than one fashion (in MySQL):

WHERE
    (id_v, lang) NOT IN
    (SELECT 
        id_v, lang
    FROM 
        ven_data v2
    ) ;

You can see the whole setup (together with all the steps 1 by 1) and you can experiment at dbfiddle here


NOTEs: you don't need to do this from a stored procedure, nor you need to use arrays. This code is quite standard SQL. Note that it also works with PostgreSQL or SQL Server, for instance.


7
  • So you are say that I should iterate over each of the pair's and not work over an array of elements? But would not it create a longer run? We are talking about something like 2-3 thousands of sql commands, not counting the original table creation. And not a mear 8 commands in the case of the array. Commented Jul 24, 2017 at 21:41
  • P.S. so as the above set, not requiering a Stored Procedure, but I would like to have one to be called if needed. Commented Jul 24, 2017 at 21:42
  • No. I say do not iterate at all. Whenever you use a database and you iterate... you're probably not using all the power of the database. The last INSERT does everything in just one (from a programmer's perspective) step. It gets some practicing, but try not to think in terms of rows. Think in terms of tables (lots of rows at once). The concept of row is straightforward if your programming in PHP, they map quite easily to one associative array. Tables aren't as straighforward and require a bit more of abstraction.
    – joanolo
    Commented Jul 24, 2017 at 21:43
  • If you need to put this into a stored procedure... it's ok. It will just have one statement.
    – joanolo
    Commented Jul 24, 2017 at 21:44
  • I see your point, but the problem is that I need all the data columns that are in addition, and they are in the 1 line I do have... Commented Jul 25, 2017 at 12:59
0

This seems to be equivalent to your two queries:

INSERT
     INTO  `ven_data`(`id_v`, `lang`, data columns) 
SELECT  `id_v`, elementInner, data columns
    FROM  `ven_data` AS v
    JOIN ( SELECT  id_v
            FROM  `ven_data`
            GROUP BY  id_v
            HAVING  COUNT(*) < 8
         ) AS x  USING(id_v)
    WHERE  `lang` = element
ON duplicate key UPDATE  lang = elementInner;

(However, I don't understand element and elementInner.

It will perform the subquery to find the 4 id_v values, then join to ven_data to get ther rest of the columns.

Please provide SHOW CREATE TABLE so I can advise on what index to have.

1
  • The element and elementInner is my way to iterate over all languages. I am just using 2 while loops on in the other to loop over all languages multiple times (outer loop is from what language the inner loop to what lanugage to insert) Commented Jul 26, 2017 at 18:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.