Stuck on an UPDATE - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Stuck on an UPDATE

Author

Topic

HoffmanGreg
Starting Member

2 Posts

Posted - 2009-02-27 : 11:42:22

I am banging my head against the wall on this one. It's probably something simple I'm just overlooking. When I run the statement below it writes the same value in every record in CUST_PRICE.

UPDATE ITEMS
SET CUST_PRICE = ICP.[SELLPRICE]
FROM ITEMS IT
JOIN ITEM_UNIT IU
ON IT.ITEMNO = IU.ITEMNO
JOIN ITEM_CUST_PRICE ICP
ON IU.ITEM_MEASURE_ID = ICP.ITEM_MEASURE_ID
WHERE IU.MEAS_TYPE = '1'
AND ICP.LEVEL_CODE = '1'

Any suggestions?
Thanks Greg

revdnrdy
Posting Yak Master

220 Posts

Posted - 2009-02-27 : 11:56:25

quote:
Originally posted by HoffmanGreg

I am banging my head against the wall on this one. It's probably something simple I'm just overlooking. When I run the statement below it writes the same value in every record in CUST_PRICE.

UPDATE ITEMS
SET CUST_PRICE = (SELECT ICP.[SELLPRICE]
FROM ITEMS IT
JOIN ITEM_UNIT IU
ON IT.ITEMNO = IU.ITEMNO
JOIN ITEM_CUST_PRICE ICP
ON IU.ITEM_MEASURE_ID = ICP.ITEM_MEASURE_ID
WHERE IU.MEAS_TYPE = '1'
AND ICP.LEVEL_CODE = '1')

Any suggestions?
Thanks Greg

See the code in red. I think you have to run a select here...

r&r

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-27 : 12:20:39

quote:
Originally posted by revdnrdy

quote:
Originally posted by HoffmanGreg

I am banging my head against the wall on this one. It's probably something simple I'm just overlooking. When I run the statement below it writes the same value in every record in CUST_PRICE.

UPDATE ITEMS
SET CUST_PRICE = (SELECT ICP.[SELLPRICE]
FROM ITEMS IT
JOIN ITEM_UNIT IU
ON IT.ITEMNO = IU.ITEMNO
JOIN ITEM_CUST_PRICE ICP
ON IU.ITEM_MEASURE_ID = ICP.ITEM_MEASURE_ID
WHERE IU.MEAS_TYPE = '1'
AND ICP.LEVEL_CODE = '1')

Any suggestions?
Thanks Greg

See the code in red. I think you have to run a select here...

r&r

Wouldn't this also update all rows with the same value ??

HoffmanGreg, You'd need to post some sample data and output you need to make this clear.

HoffmanGreg
Starting Member

2 Posts

Posted - 2009-02-27 : 14:00:42

I ran it again in Visual Studio, whoch changed my statement to this:

UPDATE ITEMS
SET CUST_PRICE = ICP.SELLPRICE
FROM ITEMS AS ITEMS_1
INNER JOIN ITEM_UNIT AS IU ON ITEMS_1.ITEMNO = IU.ITEMNO
INNER JOIN ITEM_CUST_PRICE AS ICP ON IU.ITEM_MEASURE_ID = ICP.ITEM_MEASURE_ID
CROSS JOIN ITEMS
WHERE (IU.MEAS_TYPE = '1')
AND (ICP.LEVEL_CODE = '1')

One thing I didn't mention before is that there
are 180 records but it tells me that 360 records
were updated successfully. Which makes me think
that my joins must have a problem.

My results came out the same:(I only included a few)

ITEMNO CUST_PRICE
1.8DuoCoreIntel 20.0000
101FGTIPS 20.0000
102WHEELS 20.0000
11-144-057-11 20.0000
1255 20.0000
1358 20.0000
135834 20.0000
1468 20.0000

I'm sure you know this from the statement,
There are not columns that can be joined between the
ITEM_CUST_PRICE table and the ITEMS table and that's why
I have to use the ITEM_UNIT table in between
the ITEM_CUST_PRICE table and the ITEMS table it
has a two different columns that is shares with each table.

Two other things come into play. The ITEM_UNIT table has two
records for every one record in the items table which I weed out
with the WHERE statement (IU.MEAS_TYPE = '1'). The second
is that the ITEM_CUST_PRICE table has five records for every record
in the ITEMS table and again I thought I took care of that in my WHERE
statement (ICP.LEVEL_CODE = '1').

Thanks so much for the help on this.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources