0

I have a SQLITE database with 2 table. The tables have these structure:

TABLE1 (about 2000000 records)
id|name|number1|number2|...|...
1|aaa |12.23  |43.17  |...|...
2|bbb |54.145 |77.23  |...|...
3|ccc |65.2345|13.234 |...|...

TABLE2 (about 1500000 records)
id|name|number1|number2
1|ddd |100.121|12.32
2|bbb |76.223 |11.45
3|eee |55.23  |32.1

I need do this:

  1. for each record of TABLE1, find if there is a record of TABLE2 with the same name;
  2. if YES (for example "bbb" record) I need replace "number1" and "number2" of TABLE1 (54.145, 77.23) with value of TABLE2 (76.223, 11.45);
  3. if NO (for example "aaa" and "ccc" records) I need add a random number to "number1" and "number2" of TABLE1 (12.23, 43.17 and 65.2345, 13.234). The random number must be between 0.01 and 0.30;

What is the best and faster way to do it?

Can I use a SQLITE editor? Which?

Martin
  • 1,065
  • 1
  • 17
  • 36

1 Answers1

0

A possible (untested) way to do it:

UPDATE TABLE1
SET
      number1 = (SELECT COALESCE(MAX(TABLE2.number1), (CAST(ABS((RANDOM()%30)) AS INT)+1) / 100)
                            FROM TABLE2
                            WHERE TABLE2.name = TABLE1.name),
      number2 = (SELECT COALESCE(MAX(TABLE2.number2), (CAST(ABS((RANDOM()%30)) AS INT)+1) / 100)
                            FROM TABLE2
                            WHERE TABLE2.name = TABLE1.name);

The query can be speeded up by creating indexes on the name column in both the TABLE1 and TABLE2 tables.

References:

sql: how to update table values from another table with the same user name? on SO

[sqlite] SQLite Random number generator on Google Groups

Community
  • 1
  • 1
Joseph B
  • 5,519
  • 1
  • 15
  • 19
  • This does not *add* the random numbers, and SQLite's [random](http://www.sqlite.org/lang_corefunc.html#random) does not work this way. – CL. Jul 15 '14 at 07:31
  • @CL. Thank you for the feedback! I have corrected the syntax now. First, the random number is divided by 30. The absolute value of the remainder is rounded down to the nearest integer (0-29), 1 is added (1-30) and then divided by 100 (0.01-0.30). – Joseph B Jul 15 '14 at 08:11
  • @JosephB but your query add random value if find a TABLE1 record with the same name of TABLE2, Right? if YES I don't need of it, please read again my question (I have edit it and I think now it is more clear). – Martin Jul 15 '14 at 10:28