SQLite Forum

Error Code : Error while executing SQL query on database ‘test’: no such column: price
Login
**Thanks so much for your clarification, it means a lot.**

Would you please explain the subselect? And, 

The date_back and rental_cost is default to be NULL, date_back will change until someone update new datetime string,and the rental_cost will be update by the trigger because of someone UPDATE the table.

The calculation of the rental cost is using the following formula 

rental cost = 3 + price × 0.05 × days rented

which the days rented is done by subtraction of two rows date_out - date_back
date_out & date_back is two TEXT datatype with YYYY-MM-DD HH:MM: SS string format

My friend suggested I use julianday() to do it 

And here is my edited version 

`
CREATE TRIGGER updater
         AFTER UPDATE
            ON Rental
      FOR EACH ROW
          WHEN OLD.rental_cost IS NULL AND 
               NEW.date_back IS NOT NULL
BEGIN
    UPDATE Rental
       SET rental_cost = 3 + (
                                 SELECT price
                                   FROM Rental
                                        JOIN
                                        License USING (
                                            license_id
                                        )
                                        JOIN
                                        Title USING (
                                            title
                                        )
                             )
*                            0.05 * (date_out) - (date_back) 
     WHERE rental_cost IS NULL;
END;
`



Once again, thanks so much for your help