By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,841 Members | 840 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,841 IT Pros & Developers. It's quick & easy.

SQL Update query to copy data

P: n/a
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...
Jul 20 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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
--
Jul 20 '05 #2

P: n/a
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
--

Jul 20 '05 #3

P: n/a
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
--
Jul 20 '05 #4

P: n/a
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
--

Jul 20 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.