I'm new to OOBase and SQL in general, so please bear with me!
I've got three tables: Items, Parts, and Vendors. Each entry in the Parts table corresponds with a unique part, and a vendor ID ("VID", linked to the Vendors table) for a current vendor from whom we should make future purchases for that part. Each entry in the Items table corresponds with each individual item, so if we have five "doohickey"s in stock, and the "doohickey" entry in the Parts table has a part ID ("PID") of 1, then we have five separate entries in Items, each with part ID 1.
Entries in the Items database ALSO have a vendor ID field, just like entries in the Parts database. This vendor ID corresponds with the vendor from whom we purchased that particular item, not necessarily where we should make future purchases.
Here's what I want to do: set the default value of the Items table's vendor ID (and other purchasing information) to the part's "future purchase" vendor ID from the Parts table. That is, if I don't enter where we bought this new item, just assume we bought it from our current vendor. I might need something like this:
Code: Select all
alter table "Items" alter column "VID" set default (something like: "Parts"."VID" where "Items"."PID" = "Parts"."PID")
Thank you!