Help With SQLite query

Attachment missing?

Pls try this -

Take backup of the loan table and then try -

UPDATE K
SET k.DueDate = DATEADD(d,7,DueDate)
FROM  Loan K
WHERE K.Item = 'DVD'

Fresh Taco,

Since you linked the DB schema, I am sure we can assist here but want to guide you through this versus give the answer as I think you will find it is embedded within the requirements you posted.

Write a SQL statement to update the DueDate ofthe “INFO” students who borrow DVD items in the Loan table. The new
Duedate is the checkout date plus 7 days. (Hint: (1) use the DATE function; the
due date is the CheckedOut plus 7 days; (2) Use a subquery to find the
InventoryitemID using the DVD media code.)

Before making this an UPDATE statement, start with a simple SELECT to make sure you are getting the right data.  Use aliases on all the tables then you can change to "UPDATE alias_name SET ..." like Pawan's example above once you get the data selection correct.

1. They tell you to start with Loan table and use the DueDate, so that is easy.
2. You seem to know the students are in the Customer table from the other question, so that hopefully makes sense as well.
3. What they are introducing is specific item.  Look at hint 2: it not only tells you to use a subquery, think maybe an EXISTS statement here but also the field to get.  That field appears in Loan and InventoryItem and no where else, correct?  From there, try to get to the MediaCode field and see what happens.
4. The last part is to set a new DueDate (I would put this in another column next to the current DueDate, so you can audit the change is happening correctly in the SELECT then gives you and easy way to convert to SET DueDate = {new date date here} as Pawan showed).  Look at hint 1: it tells you the function to use.  Ref: https://sqlite.org/lang_datefunc.html

In short, your query will look something like this.  I just want you to fill it in, so you understand it better.

SELECT:

SELECT LoanID, DueDate --, new due date using DATE function
FROM Loan l
WHERE EXISTS (
    SELECT 1
    FROM InventoryItem ii
    -- JOIN one or more tables here to get to MediaCode field
    WHERE ii.InventoryItemID = l.InventoryItemID
    -- AND filter media code here
)

Select allOpen in new window

UPDATE:

UPDATE l
SET DueDate --= new due date using DATE function
FROM Loan l
WHERE EXISTS (
    SELECT 1
    FROM InventoryItem ii
    -- JOIN one or more tables here to get to MediaCode field
    WHERE ii.InventoryItemID = l.InventoryItemID
    -- AND filter media code here
)

Select allOpen in new window

I hope that makes sense.

This looks like a homework.
Experts shouldn't make the homeworks for you but we can help you point you the right direction. Kevin already gave you the steps you need so good luck with your achviement.

Vitor,

this is a copy of the mailing from EE:

We have made some changes at the start of the year for homework questions.  It is ok to answer these and it is up to the Author to adhere to their own institutions policies.   I have copied the terms for this below.

https://www.experts-exchange.com/terms.jsp

A commitment to the principles of academic and vocational integrity is important to us. We strive to support the promotion of independent and original scholarship so that students derive the most from their experience and their pursuit of knowledge on our site. As a member of an academic or vocational community, you are responsible for knowing the requirements and restrictions regarding your institution’s policies, code of conduct, ethics, etc. and therefore, you may not utilize Site resources to violate those policies. Proper utilization of Site resources would be to get tutelage with assignments or projects, where they would be deemed appropriate by the institution you are attending. Each user is responsible for how they utilize Site content or Services in regards to academic or vocational integrity.

Kevin, Pawan,

you missed one important thing: SQLite does not support this kind of UPDATE query.

So the query should use just elements supported by SQLite syntax, e.g.

UPDATE loan SET DueDate = DATE(CheckedOut, '7 days')
 WHERE InventoryitemID = (SELECT InventoryitemID FROM Inventory 
                           WHERE DVDmediaCode = 'your code here')

Select allOpen in new window

Which, of course, can be updated based on the real data model which we still don't know.

Correct.  I didn't miss it initially but wanted the user to work through this as a SELECT first and so didn't adjust UPDATE.  It will be an IN statement, though, as there could be multiple if I am not mistaken.  Based on the structure I am thinking each DVD is a different item like buying Office 2016, Windows 10, et cetera.  They are different purchases but each have a product type or media code of DVD.

Plus my query is not a JOIN, so I believe I got it to work in SQLite before but think it is best if Author sticks with IN as there is a potential that answer gets marked wrong by teacher if you use syntax that is too complex (different than their grading system), so glad you reiterated pcelba.

EDIT: Confirmed this syntax works:

UPDATE Loan
SET DueDate = '2017-09-25'
WHERE EXISTS (
  SELECT 1 FROM InventoryItem ii
  WHERE ii.ItemID = Loan.InventoryItemID
);

Select allOpen in new window

Just for future readers but still think you should go with IN or = (if just one ItemID).

Yes, this was not clarified yet but I expect the DVD media code is item unique identifier (bar code) printed on each single DVD in stock independently on the title.

JOIN in a subquery should not cause problems in SQLite but possibly the fact this subquery is correlated and also UPDATE FROM is not supported in SQLite.

Hi Fresh Taco,

any news with this problem? Does it work for you already?  If yes then please select one or more posts as the solution(s).
If not then please ask for more details and/or provide the info requested by experts.

TIA
Pavel

@Author - a feedback will be appreciated.