Update Statement - 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

General SQL Server Forums

New to SQL Server Programming

Update Statement

Author

Topic

Vack
Aged Yak Warrior

530 Posts

Posted - 2008-10-21 : 10:14:25

Trying to do an Update statment which I only want ran based on a field from another table.

Tables:
OEORDHDR
OEORDLIN

LINKS:
ORD_TYPE,ORD_NO

I only want the update below to run if the field extra_10 in the oeordhdr is not zero and if the item_desc_2 in the oeordlin table is not equal 'CHANGE'

There will be one record per order number in the oeordhdr. There can be many records per order number in the oeordlin. But only one of the records will have 'CHANGE' in the item_desc_2 field.


begin
update oeordhdr_sql
set OEORDHDR_SQL.tot_sls_amt = OEORDHDR_SQL.extra_10
FROM OEORDHDR_SQL JOIN OEORDLIN_SQL ON OEORDHDR_SQL.ORD_TYPE = OEORDLIN_SQL.ORD_TYPE AND
OEORDHDR_SQL.ORD_NO = OEORDLIN_SQL.ORD_NO
where OEORDHDR_SQL.extra_10 <> 0 AND ISNULL(OEORDLIN_SQL.ITEM_DESC_2,'')<>'CHANGE'
end

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-21 : 10:19:37

What seems to be the officer, problem?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-21 : 10:20:46

[code]UPDATE ohdr
SET ohdr.tot_sls_amt = osql.extra_10
FROM OEORDHDR_SQL ohdr
JOIN OEORDLIN_SQL osql
ON ohdr.ORD_TYPE = osql.ORD_TYPE AND
ohdr.ORD_NO = osql.ORD_NO
AND osql.extra_10 <> 0
AND ISNULL(osql.ITEM_DESC_2,'')<>'CHANGE'[/code]

Vack
Aged Yak Warrior

530 Posts

Posted - 2008-10-21 : 14:07:46

It is still moving extra 10 to total sales amount. Even though I have a record in the oeordlin that had item_desc_2 = 'CHANGE'

Vack
Aged Yak Warrior

530 Posts

Posted - 2008-10-21 : 14:12:30

wHEN I RUN THIS SCRIPT THE ORDER I DO NOT WANT TO UPDATE DOES NOT SHOW IN THE LIST. BUT WHEN I RUN THE UPDATE IT IS STILL MOVING THE VALUE.

SELECT *
FROM OEORDHDR_SQL JOIN OEORDLIN_SQL ON OEORDHDR_SQL.ORD_TYPE = OEORDLIN_SQL.ORD_TYPE AND
OEORDHDR_SQL.ORD_NO = OEORDLIN_SQL.ORD_NO
where OEORDHDR_SQL.extra_10 <> 0 AND ISNULL(OEORDLIN_SQL.ITEM_DESC_2,'')<>'CHANGE'

Vack
Aged Yak Warrior

530 Posts

Posted - 2008-10-21 : 14:58:55

Figured out my issue. Since I had multiple records in the oeordlin which didn't have CHANGE in the description it was still moving my value. I needed to narrow it down a bit more to where item_no = 'RBATE' which solved my problem.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources