|
That other reply is precise but not very useful.
If you have a Mongodb where collections do not have some specific form then you are using it wrong.
Besides the CLI I have also used two different UI clients for Mongo and both show the data that are in the collections in a reasonable way.
Now if you are starting with a empty collection you won't know what will go in there and with a SQL table you would but that that is somewhat a matter of degree and not usability. In that case you should go look to your design, which you should have regardless, or the code.
|
|
|
|
|
Hello everybody.
I have no database experience so I apologize in advance if my question is too trivial.
I'm writing an application in C++ (Embarcadero C++ Builder Tokyo) and I'm using sqlite.
I have a database with a table "EVENTI" with two columns ("DataOra" and "Evento") and about 6000 rows and I want to delete the first ~5000 rows to reduce the database size to about 1000 rows.
I have to use a command like:
"DELETE FROM EVENTI WHERE (condition)"
but I don't know exactly how to write the condition.
Is there a line identifier so I can write a condition like:
"DELETE FROM EVENTI WHERE (LineId < 5000)"
Or I have to read line 5000 (how?) and build a condition on the content of that line?
Someone can help me?
Thank you
|
|
|
|
|
You'll need to use a command like
"DELETE FROM EVENTI WHERE (condition)",
but we can't tell you what that condition is, since we don't know the table structure.
You can't think of SQL tables like flat files. Successive SELECTS from a table won't necessarily return the rows in the same order.
All tables should have a PRIMARY KEY, which uniquely identifies a given row. You should be able to use this to select the rows you wish to delete. Alternatively you may have a timestamp or other identifying piece of information in the row that will help you choose which rows to delete.
|
|
|
|
|
Thank you k5054.
I have not set a PRIMARY KEY in my table but the "DataOra" column contains a timestamp in the form "YYYY/MM/DD hh:mm:ss" so I think I could use this information to select the rows to delete.
But to do this I need to read the value of row 5000 without any information about this row, I mean that I have not a PRIMARY KEY and I just know the row number, is this information enought to read row 5000?
And if I read row 5000, then I can use a command like:
"DELETE FROM EVENTI WHERE DataOra < 'YYYY/MM/DD hh:mm:ss'"
or I have to convert in some way my timestamp?
Thank you
|
|
|
|
|
You should probably use a DATETIME field type for DataOra, but if you know that all your timestamps are 'YYYY/MM/DD hh:mm:ss', then then comparing as strings should work out OK.
How you truncate your data is somewhat dependent on exactly what your need is. If you just want to delete approximately 5000 records, you could do
SELECT count(*) FROM EVENT where DataOra < 'YYYY/MM/DD 00:00:00'
and adjust the date and or time up/down until you get about 5000 and then use that date as your comparison. Or maybe you would be happy with just deleting records before Feb 01/2019 in which case you could just use '2019/02/01 00:00:00' as your comparison
Alternatively, if you want to delete exactly 5000 records, ordering by timestamps, then this might work for you:
CREATE TEMP TABLE TMP(id INTEGER PRIMARY KEY AUTOINCREMENT, DataOra text);
INSERT INTO TMP(DataOra) SELECT DataOra from EVENT order by DataOra;
DELETE FROM EVENT where DataOra <= (SELECT DataOra from TMP where id = 5000);
hope this helps. Don't forget to back up your data before you begin - just in case!
|
|
|
|
|
Sorry for the delay.
Thank you for your detailed answer.
Where is created the TMP table? In the same database where there is the EVENTI table?
Thanks.
|
|
|
|
|
In my example the TMP table is created with the attribute TEMP, which means that the table will be TEMPORARY, and are dropped when the connection that created them closes. TEMP tables are created in the "temp" database (as per sqlite docs). Other than only existing for the current session, TEMP tables are just like regular tables - they can be indexed, altered, updated, etc.
|
|
|
|
|
Thank you very much k5054 for your explanation! 
|
|
|
|
|
If "DataOra" or "Evento" are not your line numbering system then you have a problem.
Assuming there is some sort of sequence in your data, date or number you could select the top 50000, get the sequence value and then delete anything less than the selected sequence. I would test this on a copy of the database 
select - How to get Top 5 records in SqLite? - Stack Overflow[^] add an orderby your sequence field and you get the values for your where condition.
Never underestimate the power of human stupidity -
RAH
I'm old. I know stuff - JSOP
|
|
|
|
|
Thank you Mycroft Holmes.
My DataOra column contains a timestamp in the form "YYYY/MM/DD hh:mm:ss", as I answered k5054, so this seems a good reference.
The data in my table is ordered, it is a sequence of events, so if I say
"SELECT * FROM EVENTI ORDER BY DataOra ASC LIMIT 5000"
I really select the first 5000 rows?
How I put the result of SELECT in my WHERE condition?
Thank you.
|
|
|
|
|
Now get the latest date from the result set and delete all record where the date is less than the latest date.
Never underestimate the power of human stupidity -
RAH
I'm old. I know stuff - JSOP
|
|
|
|
|
|
Thank you Richard for your helpfull suggestion.
|
|
|
|
|
given a table job with columns time_utc (datetime) and state (integer), i want to find the periods (start-time and end_time) where the consequetive value of the state column equals 4.
i tried to find the start of the periods with the folllowing query, but no success. too many results.
can someone please help me? i cannot figure it out.
SELECT act_start_time_utc, state_cd FROM SRPMES901007.WWMESDB.dbo.job WHERE act_start_time_utc IS NOT NULL ORDER BY act_start_time_utc ASC
DECLARE @PERIOD TABLE (start_time DATETIME, end_time DATETIME)
INSERT INTO @PERIOD(start_time)
SELECT t.start_time FROM (
SELECT act_start_time_utc AS start_time, state_cd AS new_state, LAG(state_cd, 1) OVER (ORDER BY act_start_time_utc ASC) AS last_state
FROM SRPMES901007.WWMESDB.dbo.job
WHERE act_start_time_utc IS NOT NULL) AS t
WHERE t.new_state = 4 AND t.last_state <> 4
UPDATE period
SET end_time = (SELECT MIN(act_start_time_utc) endtime FROM SRPMES901007.WWMESDB.dbo.job INNER JOIN @PERIOD period ON job.act_start_time_utc > period.start_time AND state_cd <> 4)
FROM @PERIOD period
SELECT * FROM @PERIOD ORDER BY start_time ASC
modified 9-Feb-19 16:10pm.
|
|
|
|
|
i finally figured it out, here's the result:
DECLARE @PERIOD TABLE (start_time DATETIME, end_time DATETIME)
INSERT INTO @PERIOD(start_time)
SELECT t.start_time FROM (
SELECT act_start_time_utc AS start_time, state_cd AS new_state, LAG(state_cd, 1) OVER (ORDER BY act_start_time_utc ASC) AS last_state
FROM SRPMES901007.WWMESDB.dbo.job
WHERE act_start_time_utc IS NOT NULL) AS t
WHERE t.new_state = 4 AND t.last_state <> 4
UPDATE period
SET end_time = (SELECT TOP(1) act_start_time_utc FROM SRPMES901007.WWMESDB.dbo.job WHERE state_cd <> 4 AND act_start_time_utc > period.start_time ORDER BY act_start_time_utc ASC)
FROM @PERIOD period
SELECT * FROM @PERIOD ORDER BY start_time ASC
But the query takes 19 seconds, can it be speeded up?
modified 11-Feb-19 4:38am.
|
|
|
|
|
hi,
i am having trouble with a query. how can i update a temp table with results from sub query (actually it is a function)?
this works:
UPDATE @TEMP
SET
formula_name = (SELECT name FROM dbo.fn_GetFormulaForJob(wo_id, oper_id, seq_no)),
formula_version = (SELECT version FROM dbo.fn_GetFormulaForJob(wo_id, oper_id, seq_no))
FROM @TEMP
but i want something like this:
UPDATE @TEMP
SET formula_name = r.name,
formula_version = r.version
FROM (SELECT name, version FROM dbo.fn_GetFormulaForJob(wo_id, oper_id, seq_no) r
thanks.
modified 7-Feb-19 10:40am.
|
|
|
|
|
Treat it the same as a table
UPDATE @temp
SET formula_name = r.name, formula_version = r.version
FROM dbo.fn_GetFormulaForJob(wo_id, oper_id,seq_no) r
Everyone is born right handed. Only the strongest overcome it.
Fight for left-handed rights and hand equality.
|
|
|
|
|
thanks. i tried it, but i get the error 'Cannot call methods on table'.
too bad. my original looks so inefficient, but it will have to do i guess.
|
|
|
|
|
|
wow, ok, i will try it first thing in the morning. thanks.
|
|
|
|
|
this works! Thanks.
modified 8-Feb-19 2:41am.
|
|
|
|
|
Hi!
Yesterday i used your app sqlite compare to compare 2 databases.
My problem is that there is two tables with the same data, but the program says that one is on the left side and the other one is on the right side. Any idea ?
|
|
|
|
|
Member 14136263 wrote: i used your app sqlite compare
Assuming you are talking about some CodeProject article, please post your question in the forum below the article. Only then is the author likely to see your message.
|
|
|
|
|
Member 14136263 wrote: Any idea ?
No.
1. This site does not have a sqlite compare product. There might be an article that one of the 14 million members wrote.
2. One on the left and one on the right. Yes, because that's how you compare side to side.
So, this is the wrong place but even still your question does not make any sense.
Everyone is born right handed. Only the strongest overcome it.
Fight for left-handed rights and hand equality.
|
|
|
|
|
I have an Azure SQL database that uses the Always Encrypted functionality. In this database I have a column called entryObject. It is an nvarchar field containing JSON data. It is encrypted using a Randomized encryption type.
I am currently writing SQL that should us the JSON_MODIFY function to update this data as well as unencrypted data in another table. I would like to have all of this functionality wrapped in a transaction and in a stored procedure. This way, my C# code can call this one stored proc and, if successful, I'll know that all data was updated successfully. However, any time I try to use the JSON_MODIFY function or even JSON_QUERY with my encrypted entryObject column's data, I get an error stating
Argument data type nvarchar(max) encrypted with (encryption_type = 'RANDOMIZED', ... is invalid for argument 1 of json_query function.
How can I use JSON_MODIFY to change one of the values in this encrypted JSON?
Thanks in advance for any assistance you can give.
Denise
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
