Swap fields in a SQL DB

[Garling]

I have a SQL DB that has a table named: InventoryUOM
With the following data.

Num UOM Conv1 Conv2 Price Cost
1 Box 1 1 0.08 2.19
1 Each 100 1 4.87 2.19

What I need to do is have the box price swap with the each price. I am trying to get a query to run this.

Any ideas?

 

[SQLSister]

makea backup before you do this.

I would put the data you want updated into a separate temp table first.

Then join to this table in the updates

so suppose the data is in #temp

update t1
set price = t.price
from table1 t1
join #temp t on t1.num = 1.num
where t1.UOM = 'box' and t.UOM = 'each'
and t.price<>t.price

update t1
set price = t.price
from table1 t1
join #temp t on t1.num = 1.num
where t1.UOM = 'each' and t.UOM = 'box'
and t.price<>t.price

Do not do this on production without creating a backup of the table data first. Even on dev it is best if you preserve an old copy of the data so you can really make sure you updated the right thing. This is untested code and may not be exactly what you need. So please do not do anything this drastic to your data without some controls in place to get back if something goes wrong.



"NOTHING is more important in a database than integrity." ESquared

 

[Garling]

I have tried this:

update inventoryUOM
set item_price = inventoryUOM_Test.Item_price
from inventoryUOM
join inventoryUOM_Test on itemnum = inventoryUOM_Test.itemnum
where UnitOfMeasure = 'box' and inventoryUOM_Test.UnitOfMeasure = 'each'
and Item_price<>Item_price

But I am getting these errors:

ERROR: Ambiguous column name 'UnitofMeasure'.
ERROR: Ambiguous column name 'Item_Price'.
ERROR: Ambiguous column name 'Item_Price'.

 

[SQLSister]

that measn you need to specify which table to choose those fileds from as they are in more than one.

"NOTHING is more important in a database than integrity." ESquared

 

[djj55]

You would need an alias.

 

[Garling]

This is what I was given that worked, if any ever needs something like this again.

CREATE TABLE #InventoryUOM

(Company char(1), StoreID numeric(5), itemnum numeric(9), UnitofMeasure char(20), Item_Price numeric(9,2))

 

INSERT INTO #InventoryUOM

SELECT InventoryUOM.Company, InventoryUOM.StoreID, InventoryUOM.itemnum, InventoryUOM.UnitofMeasure,

       InventoryUOM.Item_Price

  FROM InventoryUOM

  JOIN Inventory    AS I ON InventoryUOM.Company = I.Company

                        AND InventoryUOM.StoreID = I.StoreID

                        AND InventoryUOM.itemnum = I.Itemnum

 WHERE I.Manufacturer = 'Midwest Fasteners'

 

UPDATE InventoryUOM

   SET Item_Price = U.Item_Price

--SELECT InventoryUOM.*, U.Hold

  FROM InventoryUOM

  JOIN #InventoryUOM AS U ON InventoryUOM.Company = U.Company

                         AND InventoryUOM.StoreID = U.StoreID

                         AND InventoryUOM.itemnum = U.Itemnum

                         AND InventoryUOM.UnitOfMeasure <> U.UnitOfMeasure

 

DROP TABLE #InventoryUOM

 

[ESquared]

That query assumes you only have two unit of measures. That's a big assumption. Careful with that query down the road when you have more.

 

[ESquared]

* units of measure