I have a table where I need to update or insert depending on field paramaters

Question

I have spent many hours researching this problem and trying various solutions but I never quite find a suitable solution for my specific problem. I am new to SQL and some of the examples are confusing as well.

So here is my dilemma. I have a equipment table that tracks oil changes for specific units in a database. The table looks like this:

     **id   UnitID   Posted_On    Date_Completed     Note     OverDueBy**
      1 BT-109F  2019-02-04   2019-02-14       Hrs  Overdue   23 
      1 BT-108G  2020-01-17   2020-01-22       Days Overdue   12
      1 BT-122K  2020-01-02   2020-01-16       Days Overdue   12
      1 BT-109F  2019-02-04                    Days Overdue   3

The example records above need to be created or updated by the query. The date completed is entered manually by the technician when he has completed the oil change.

What I want the query to do is, Check to see if a specific Unit has a record where the 'Date_Completed' field is empty, and if so update the 'OverDueBy' field to reflect the new value. If all the records for the specified Unit have the 'Date_Completed' fields filled in, then the query should create a new record will all fields filled in except for the 'Date_Completed' field.

Can anyone help me construct such a query?

Thanks Clan

If it is really sqlite I do not think you can, what you need is more like a stored procedure rather than a query and sqlite does not support stored procedures. What I would do in your place I would write 3 separate query and a piece of python to run them according to the logic you mentioned. — Marco, Feb 04 '20 at 17:29
If your `sqlite` version supports so called UPSERT then this is way to go https://stackoverflow.com/a/4330694/8339821. — user14063792468, Feb 04 '20 at 17:37
I am sure UPSERT works with my sqlite and I have tried to format a query using the examples but I am not experienced enough to get it right. Anyone? — Clan, Feb 04 '20 at 17:58
This isn't what upsert is for. That's triggered by primary key or unique index violations. — Shawn, Feb 04 '20 at 18:11
Personally, I'd just calculate the overdue value on the fly (maybe in a view or if you're using the latest sqlite, a generated column) instead of storing it and having to update it every day. — Shawn, Feb 04 '20 at 18:12

score 1 · Accepted Answer · answered Feb 04 '20 at 19:13

1

First create a unique partial index for the column UnitID:

CREATE UNIQUE INDEX idx_unit ON tablename(UnitID) 
WHERE Date_Completed IS NULL;

so that only 1 row with Date_Completed=null is allowed for each UnitID.
So a statement like this:

INSERT INTO tablename(id, UnitID, Posted_On, Date_Completed, Note, OverDueBy)
VALUES (?, 'BT-109F', ?, null, ?, ?)
ON CONFLICT(UnitID) WHERE Date_Completed IS NULL DO UPDATE
SET OverDueBy = ?;

will insert the new values only if there is no row already for UnitID='BT-109F' with null in Date_Completed.
But if there is such a row then it will update the column OverDueBy.
I'm not sure what values you want to insert or what will be the updated value so replace the ? with the appropriate values.

answered Feb 04 '20 at 19:13

forpas

160,666
10
38
76

Thanks. I will try this tomorrow. (Have to go out for the rest of the afternoon). I will let you know how this works. :) – Clan Feb 04 '20 at 20:46
I did create the index but the second part does not work. I get the following message: ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint. Not sure what to try next. – Clan Feb 05 '20 at 15:46
Did you copy the code as it is with ` WHERE Date_Completed IS NULL`? The syntax is correct. Check this: https://www.db-fiddle.com/f/2VNXYCUErV3ygFBgyKjSpJ/1 – forpas Feb 05 '20 at 15:51
I did but I will check out the link and get back to you. Again we have to go out so I will not be able to get back to you til tomorrow morning at the earliest. (Family member in the hospital) – Clan Feb 05 '20 at 16:11
@forpasThank you so much. This worked perfectly. I am amazed that you came up with this solution. It would not have occured to me to use a unique index. Very clever. :) – Clan Feb 06 '20 at 12:56

score 0 · Answer 2 · answered Feb 04 '20 at 18:11

Firstly I would use a view rather than a table to store any calculated data - it reduces storage overheads and will update the calculation every time the view is opened. If you're using SQLite you should be able to get the overdue by subtracting the Posted_On from its function to return today's date something like date('now') or julianday('now') - read up on and test the functions to ensure it does what you want. So along the lines of:-

create view MyView as select *,  julianday('now') - julianday(Posted_On) as OverDueBy from ClansTable where Date_Completed is null;

If you want to store a snapshot you can always create a table from a view in any case:-

create table MyStoredOverduesOn4thFeb as select * from MyView;

You can find your units that have all Date_Completed and create a single new record like so:-

Create table CompletedUnits as select id, UnitID, max(posted_on) as latest_posted_on, '' as Date_Completed from ClansTable group by id, UnitID having count(*) = count(Date_Complete);

Test this SQL and see if you can get it working - note I've created a text field for the date. Apparently there is no date/datetime data type as such:-

https://www.sqlitetutorial.net/sqlite-date/

Hope this helps,

Phil

I appriciate your help and this will work for the field when it is overdue by date, but the units can also be overdue by the hourmeter reading. The query will be run by a node-red function once a day. If the PMI (Oil change) is not completed, then the query is (I hope) going to update the current hour meter reading into the field. — Clan, Feb 04 '20 at 18:23
I guess I could do a 'select' statement filtering for Unit and IsNull('Date_Completed) and then if I get an error then insert otherwise update. (Just do not know how to do this) — Clan, Feb 04 '20 at 18:28
Ok, so how about using datetime() functions instead? https://www.techonthenet.com/sqlite/functions/datetime.php — blake, Feb 04 '20 at 18:29
I do use datetime functions in the node-red function but the query is a different story — Clan, Feb 04 '20 at 20:47

score 0 · Answer 3 · answered Feb 04 '20 at 18:50

I think you need something like this:

MERGE INTO EQUIPMENT A 
USING (SELECT * FROM EQUIPMENT B WHERE DATE_COMPLETED IS NULL) C 
ON (A.UNITID=C.UNITID)
WHEN MATCHED THEN UPDATE SET  A.OVERDUEBY="new value"
WHEN NOT MATCHED THEN INSERT (A.id,A.UnitID,A.Posted_On,A.Date_Completed,A.Note,A.OverDueBy)
VALUES (C.id,C.UnitID,C.Posted_On,NULL,C.Note,C.OverDueBy)

Not sure where new values from update will come from. It's not clear in your question. But something like this could work.

SQLite does not support MERGE. – forpas Feb 04 '20 at 18:56 — forpas, Feb 04 '20 at 18:56

I have a table where I need to update or insert depending on field paramaters

3 Answers3