SQL Update query to copy data

I'm a bit of a newby to creating update queries so I was hoping those more
experienced could help with what should be a simple query. I do know to
create backups and test on a test database first.

I need to copy an items' cost from location 'CA' to the cost in location
'OH' when the cost in 'CA' is not zero
Table name is ITEMS
Location column is LOC
Cost column is COST

Here's the Select statement from Enterprise Manager's Open Table, Query tool

SELECT loc AS Expr1, cost AS Expr2
FROM ITEMS
WHERE (loc = 'CA') AND (cost <> 0)
I just can't seem to get this into a working Update query.

Thanks in advance...

[David Portas]

Assuming there is no more than one row where loc = 'CA' in the Items table:

UPDATE Items
SET cost =
(SELECT cost
FROM Items
WHERE loc = 'CA'
AND cost<>0)
WHERE loc = 'OH' AND EXISTS
(SELECT *
FROM Items
WHERE loc = 'CA'
AND cost<>0)

If the Costs come from more than one row then you'll have to explain which
rows you want to populate with what data. Maybe you could post DDL (CREATE
TABLE statement) for the table and gave us some example data to work with.

--
David Portas
------------
Please reply only to the newsgroup
--

Yikes, I know even less than I thought. The table does have multiple rows
where loc = 'CA'
Here's some sample data:

RECORD ITEM LOC
COST
======= ==== ===
====
1 486 PAR CA
785
2 486 PAR OH
549
3 486 KIT OH
<NULL>
4 486 KIT CA
823
5 CASE OH
0
6 CASE CA
39
7 KIT-DOS CA
0
8 KIT-DOS OH
975
So for example, I want to copy/replace the COST of record 1 to the COST of
record 2, record 4 to record 3, record 6 to record 5, but not record 7 to
record 8 since the cost of 7 is zero.

Thanks again.

Assuming there is no more than one row where loc = 'CA' in the Items table:
UPDATE Items
SET cost =
(SELECT cost
FROM Items
WHERE loc = 'CA'
AND cost<>0)
WHERE loc = 'OH' AND EXISTS
(SELECT *
FROM Items
WHERE loc = 'CA'
AND cost<>0)

If the Costs come from more than one row then you'll have to explain which
rows you want to populate with what data. Maybe you could post DDL (CREATE
TABLE statement) for the table and gave us some example data to work with.

--
David Portas
------------
Please reply only to the newsgroup
--

[David Portas]

Can I assume that the combination of (Item, Loc) is always unique, as in
your sample? If so, try this:

UPDATE Items
SET cost =
(SELECT cost
FROM Items AS I
WHERE loc = 'CA' AND item = Items.item
AND cost<>0)
WHERE loc = 'OH' AND EXISTS
(SELECT *
FROM Items
WHERE loc = 'CA' AND item = Items.item
AND cost<>0)

--
David Portas
------------
Please reply only to the newsgroup
--

That did the trick !!
Thank you very much for your help.

Can I assume that the combination of (Item, Loc) is always unique, as in
your sample? If so, try this:

UPDATE Items
SET cost =
(SELECT cost
FROM Items AS I
WHERE loc = 'CA' AND item = Items.item
AND cost<>0)
WHERE loc = 'OH' AND EXISTS
(SELECT *
FROM Items
WHERE loc = 'CA' AND item = Items.item
AND cost<>0)

--
David Portas
------------
Please reply only to the newsgroup
--