1. Home
  2. Point-Of-Sale Software
  3. SQL Query to set SupplierList Cost to equal ItemCost?

SQL Query to set SupplierList Cost to equal ItemCost?

What SQL Query can I use to set the SupplierListCost to equal the ItemCost? We have a supplier where these should match and I'd rather not go item by item.

Reply to
pete
Loading thread data ...

Hi Pete - first query the supplier table to find out the supplier's ID

select id, suppliername from supplier

-- jot down the id assigned to the supplier whose costs you want affected in the supplierlist table - - let's say the ID is 11

Its usually a good idea to run a test query to make sure you're hitting the records that need updating....something like

select i.itemlookupcode, i.description, i.cost as item_cost, sl.cost as supplierlist_cost from item i inner join supplierlist sl on i.id = sl.itemid where sl.supplierid = 11

If your results show the records you want to update, BACKUP YOUR DATABASE - then run

update supplierlist set supplierlist.cost = item.cost from supplierlist inner join item on supplierlist.itemid = item.id where supplierlist.supplierid = 11

remember the backup! - h> What SQL Query can I use to set the SupplierListCost to equal the ItemCost?

Reply to
convoluted

BeanSmart website is not affiliated with any of the manufacturers or service providers discussed here. All logos and trade names are the property of their respective owners.