SQL - Update multiple columns in several rows of the same table

Question

SQL - Update multiple columns in several rows of the same table

Navigation

#1 by (1 votes)

0

I work with NodeJS and SQLite , seeing my use of the I / O of the SSD disks on my Azure server (which were extremely high), I would like to make a serious optimization.

My table has the following format:

ID                       points         money        rep
'242043489611808769'     81086          6150         19
'158341246635606016'     62976          5604         2
'204425188480122881'     33140          0            0
'211591629486686208'     13375          2250         0
'202137748885340160'     31182          1000         7
'159730926161625088'     42907          200          0
'207174964468645889'     1243           10669        3
'179424336942202880'     12162          0            0
'170716616659566593'     13312          650          1

During all this time, I had always been running queries for each line, but it seems to be both recursive and very inefficient.

UPDATE users SET points = '81086' WHERE ID = '242043489611808769'

And when I needed to update multiple columns, I would execute the following:

UPDATE users SET points = '81086', money = '6150' WHERE ID = '242043489611808769'

In addition to having a cache system that allows me to simplify / optimize queries, in addition to preventing the error SQLITE_BUSY . The system updates the database every 30 seconds, and usually has 3-7 columns.

However, now I would like that, instead of sending 7 queries, send 1 only, updating 7 rows with the possibility of multiple columns. For example, update the previous table to:

ID                       points         money        rep
'242043489611808769'     86086          6150         21
'158341246635606016'     67976          5604         2
'204425188480122881'     43140          257          0
'211591629486686208'     13375          2250         12
'202137748885340160'     31182          1000         7
'159730926161625088'     42907          200          1
'207174964468645889'     1743           10669        4
'179424336942202880'     12162          130          2
'170716616659566593'     14312          650          1

In which 1-3 columns have been edited in each row.

Thanks in advance.

sql nodejs sqlite

asked by Antonio Roman 18.04.2017 в 20:17

source

1 answer

New char in c ++ gives me long wrong Drop down list with PDO [closed]

score 1 · Accepted Answer

I found the solution. First: UPDATE <table> , in this case users . Then, update a column ( points ) with a CASE..WHEN , with the format WHEN <ID> THEN <Value> , where <ID> is the row that you want to update (similar to WHERE <ID> ), and <Value> would be the new value of the row, in the selected column.

UPDATE users
SET points = CASE id
    WHEN '242043489611808769' THEN 90000
    WHEN '158341246635606016' THEN 65000
    WHEN '202245579718262784' THEN 52000
END,
money = CASE id
    WHEN '242043489611808769' THEN 4000
    WHEN '158341246635606016' THEN 9000
    WHEN '202245579718262784' THEN 15000
END
WHERE id IN ('242043489611808769','158341246635606016','202245579718262784')

In summary, update the table users , select the column points with a CASE in which, when ID="x", then update its value to "y". End the case with a END, , and do the same to update another column, (and end with another END ).

Finally, specify in which rows have been updated.

Source in English