Little Question about DB (postgres)
Hello, I have a table "Flights"
I wanna update "Status" column, when "Scheduled_Departure" is already happened, can i do that in database logic ?
14 Replies
sure
UPDATE [tablename] SET status = 'your value' WHERE scheduled_departure < now() should do the trickwow, so simple ?
yeah?
this would ofc change the value of every single thing in this table that matches the criteria
so you might need to add a few more conditions
like, only affect ones that are currently in status
scheduled or somethingbut can I apply "my conditions" to the whole table directly in database ?
like auto-updating table
wdym?
no
tables are not auto updating
you run queries
you can use a scheduled job to run a specific query at certain intervals, but honestly, just model your code better
in a situation like this, you wouldn't have a status field
you'd calculate it when you fetch the row
for any given flight, if the
scheduled_departure is in the past, the flight is departed, unless the cancelled_at field has a value
in that case, its cancelled.ok, i got it, thank you!
downside of this is you can't query on a certain status, without knowing the conditions for that status
for my task
UPDATE [tablename] SET status = 'your value' WHERE status='arrived' AND scheduled_departure < now() is enough, at least so farsure, but it will only update things as your query fires
which means you might have an incorrect status on a flight at times
Did you mean, that query like
SELECT * FROM [TABLENAME] WHERE status='arrived', may work incorrectly, cause it was executed between my updating intervals ?no I mean if you remove the
status field and calculate it in C# based on other propertiesBut why the 'status' field is bad to have ?
because if you dont run the update query very often, you might end up having faulty statuses
you could use a virtual generated column if you really wanted that logic to "just happen" on the database side but i wouldn't expect good query performance from that
also, pretty optimistic to assume flights always leave exactly on time :when: