# How to make this SQL insert more efficient?

**URL:** <https://discourse.nodered.org/t/how-to-make-this-sql-insert-more-efficient/10528>\
**Category:** General\
**Created:** [25 April 2019 08:02 UTC](https://discourse.nodered.org/t/how-to-make-this-sql-insert-more-efficient/10528 "2019-04-25T08:02:44Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![swiftnesses](https://avatars.discourse-cdn.com/v4/letter/s/f17d59/32.png) [@swiftnesses](https://discourse.nodered.org/u/swiftnesses)\
**Post date:** [25 April 2019 08:02 UTC](https://discourse.nodered.org/t/how-to-make-this-sql-insert-more-efficient/10528/1 "2019-04-25T08:02:44Z")

</div>

Good day,

I have a flow which dynamically creates SQL params and updates some values in a table.

Depending on the some criteria, I need to update the daily, weekly, monthly, yearly or all time high score. Currently I have the following SQL code (prepared):

```auto
INSERT INTO liquidation_high_scores (
    exchange,
    symbol,
    currency, 
    day_high_score,
    week_high_score,
    month_high_score,
    year_high_score,
    all_time_high_score
) 
values (
    $exchange,
    $symbol,
    $currency, 
    $day_high_score,
    $week_high_score,
    $month_high_score,
    $year_high_score,
    $all_time_high_score
)

ON CONFLICT(exchange, symbol, currency) DO UPDATE SET 
    exchange = $exchange,
    symbol = $symbol,
    currency = $currency,
    day_high_score = $day_high_score,
    week_high_score = $week_high_score,
    month_high_score = $month_high_score,
    year_high_score = $year_high_score,
    all_time_high_score = $all_time_high_score;

```

My table looks like this:

```auto
CREATE TABLE liquidation_high_scores
    (
        exchange TEXT,
        symbol TEXT,
        currency TEXT,
        day_high_score INTEGER,
        week_high_score INTEGER,
        month_high_score INTEGER,
        year_high_score INTEGER,
        all_time_high_score INTEGER,
        PRIMARY KEY (exchange, symbol, currency)
    )

```

The issue is, unless a param is populated, i.e. only `day_high_score` is included in the params, the other values are overwritten with NULL. To workaround this, I've created a function with 5 outputs (one for day, week, month, year, all\_time), and have connected the SQL node to each with code containing only the correct params. Obviously this is a little messy to manage, I wondered if there's a better way to achieve this, perhaps insert a value ONLY if included in the params, and avoid writing NULL for ones which are not?

I'm also not convinced my table is setup right, in essence, the combination of exchange, symbol and currency should be unique to each row... Duplicates should not be allowed.

Many thanks in advance for any help.

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [25 April 2019 08:31 UTC](https://discourse.nodered.org/t/how-to-make-this-sql-insert-more-efficient/10528/2 "2019-04-25T08:31:34Z")

</div>

I don't understand your workaround, If I understand correctly, in the example you gave, where you only have day\_high\_score you have created an INJECT statement that only includes that column. However, since each row in the table contains all the columns the end result will be the same, a row with only day\_high\_score populated and all the rest empty.  
Can you explain exactly what you want written to the table when you only have day\_high\_score available?

---

<div class="post-metadata">

**Author:** ![swiftnesses](https://avatars.discourse-cdn.com/v4/letter/s/f17d59/32.png) [@swiftnesses](https://discourse.nodered.org/u/swiftnesses)\
**Post date:** [25 April 2019 08:33 UTC](https://discourse.nodered.org/t/how-to-make-this-sql-insert-more-efficient/10528/3 "2019-04-25T08:33:56Z")

</div>

@Colin ,

My workaround looks like this (excuse my terrible code):

Function node:

```auto
// Initilise variables

var symbol = msg.template.content.symbol
var currency = msg.template.content.currency
var exchange = 'BitMEX'

var day_high_score = msg.payload[0].day_high_score
var week_high_score = msg.payload[0].week_high_score
var month_high_score = msg.payload[0].month_high_score
var year_high_score = msg.payload[0].year_high_score
var all_time_high_score = msg.payload[0].all_time_high_score

var new_day_high_score = false
var new_week_high_score = false
var new_month_high_score = false
var new_year_high_score = false
var new_all_time_high_score = false

var units = msg.template.content.units

var params

// Check current units against high scrores

if (units > day_high_score) {
    node.error("BitMEX: New daily high score!")
    new_day_high_score = true
}

if (units > week_high_score) {
    node.error("BitMEX: New weekly high score!")
    new_week_high_score = true
}

if (units > month_high_score) {
    node.error("BitMEX: New monthly high score!")
    new_month_high_score = true
}

if (units > year_high_score) {
    node.error("BitMEX: New yearly high score!")
    new_year_high_score = true
}

if (units > all_time_high_score) {
    node.error("BitMEX: New all time high score!")
    new_all_time_high_score = true
}

// Create SQL params

if (new_all_time_high_score === true) {
    params = {
        $exchange:exchange,
        $symbol:symbol,
        $currency:currency,
        $day_high_score:units,
        $week_high_score:units,
        $month_high_score:units,
        $year_high_score:units,
        $all_time_high_score:units
    }

    msg.template.content.high_score = "all time"
    msg.params = params
    return [null, null, null, null, msg]
}

if (new_year_high_score === true) {
    params = {
        $exchange:exchange,
        $symbol:symbol,
        $currency:currency,
        $day_high_score:units,
        $week_high_score:units,
        $month_high_score:units,
        $year_high_score:units
    }
    
    msg.template.content.high_score = "year"
    msg.params = params
    return [null, null, null, msg, null]
}

if (new_month_high_score === true) {
    params = {
        $exchange:exchange,
        $symbol:symbol,
        $currency:currency,
        $day_high_score:units,
        $week_high_score:units,
        $month_high_score:units
    }
    
    msg.template.content.high_score = "month"
    msg.params = params
    return [null, null, msg, null, null]
}

if (new_week_high_score === true) {
    params = {
        $exchange:exchange,
        $symbol:symbol,
        $currency:currency,
        $day_high_score:units,
        $week_high_score:units
    }
    
    msg.template.content.high_score = "week"
    msg.params = params
    return [null, msg, null, null, null]
}

if (new_day_high_score === true) {
    params = {
        $exchange:exchange,
        $symbol:symbol,
        $currency:currency,
        $day_high_score:units
    }
   
    msg.template.content.high_score = "day"
    msg.params = params
    return [msg, null, null, null, null]
}

```

I have then connected an SQL node to each output, for example:

```auto
INSERT INTO liquidation_high_scores (
    exchange,
    symbol,
    currency, 
    day_high_score
) 
values (
    $exchange,
    $symbol,
    $currency, 
    $day_high_score
)

ON CONFLICT(exchange, symbol, currency) DO UPDATE SET 
    exchange = $exchange,
    symbol = $symbol,
    currency = $currency,
    day_high_score = $day_high_score;

```

OR

```auto
INSERT INTO liquidation_high_scores (
    exchange,
    symbol,
    currency, 
    day_high_score,
    week_high_score,
    month_high_score,
    year_high_score,
    all_time_high_score
) 
values (
    $exchange,
    $symbol,
    $currency, 
    $day_high_score,
    $week_high_score,
    $month_high_score,
    $year_high_score,
    $all_time_high_score
)

ON CONFLICT(exchange, symbol, currency) DO UPDATE SET 
    exchange = $exchange,
    symbol = $symbol,
    currency = $currency,
    day_high_score = $day_high_score,
    week_high_score = $week_high_score,
    month_high_score = $month_high_score,
    year_high_score = $year_high_score,
    all_time_high_score = $all_time_high_score;

```

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [25 April 2019 09:58 UTC](https://discourse.nodered.org/t/how-to-make-this-sql-insert-more-efficient/10528/4 "2019-04-25T09:58:30Z")

</div>

What does the SQL look like if only one param?

---

<div class="post-metadata">

**Author:** ![swiftnesses](https://avatars.discourse-cdn.com/v4/letter/s/f17d59/32.png) [@swiftnesses](https://discourse.nodered.org/u/swiftnesses)\
**Post date:** [25 April 2019 10:05 UTC](https://discourse.nodered.org/t/how-to-make-this-sql-insert-more-efficient/10528/5 "2019-04-25T10:05:28Z")

</div>

Hi Colin, not sure I understand you, the upper most SQL query updates only 1 param (day)?

Obviously I didn't post the other queries, they're just the same bar the `_high_score` params.

Basically I wondered if there's a way to use the full query (with all `_high_score` values), but only insert if the param isn't NULL?

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [25 April 2019 10:57 UTC](https://discourse.nodered.org/t/how-to-make-this-sql-insert-more-efficient/10528/6 "2019-04-25T10:57:11Z")

</div>

What SQL is this? mysql, sqlite? mssql?

Why not dynamically build the 'on conflict' portion of the sql statement based on which value is coming in? That way you would only update that single column?

---

<div class="post-metadata">

**Author:** ![swiftnesses](https://avatars.discourse-cdn.com/v4/letter/s/f17d59/32.png) [@swiftnesses](https://discourse.nodered.org/u/swiftnesses)\
**Post date:** [25 April 2019 10:58 UTC](https://discourse.nodered.org/t/how-to-make-this-sql-insert-more-efficient/10528/7 "2019-04-25T10:58:19Z")

</div>

Hi,

It's SQLite, that's what I want to do, but have no experience / skill to do it... ☹

---

<div class="post-metadata">

**Author:** ![moebius](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/moebius/32/2872_2.png) [@moebius](https://discourse.nodered.org/u/moebius)\
**Post date:** [25 April 2019 11:09 UTC](https://discourse.nodered.org/t/how-to-make-this-sql-insert-more-efficient/10528/8 "2019-04-25T11:09:41Z")

</div>

You could try to normalize your data model...  
First step would be to create tables for the different high scores.

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [25 April 2019 11:15 UTC](https://discourse.nodered.org/t/how-to-make-this-sql-insert-more-efficient/10528/9 "2019-04-25T11:15:25Z")

</div>

Well lucky you, you get to learn something new! I would start be reading about the `sqlite update` statement ([https://www.tutorialspoint.com/sqlite/sqlite\_update\_query.htm](https://www.tutorialspoint.com/sqlite/sqlite_update_query.htm)) then layout your generic update statement.

you could build your update in 'chunks' like

```auto
sql_command = 'the first part'
sql_where = 'where clause'
if condition1 sql_set = 'values to update on condition 1'
if condition2 sql_set = 'values to update on condition 2'
.
if conditionN sql_set = 'values to update on condition n'
sql = sql_command + sql_set + sql_where

```

make sure to aadd a debug to what ever node you use to create the sql so you can see if it 'looks' correct.