• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 319
  • Last Modified:

Update within a price range in SQL Server

I keep a master inventory list in table 'BookList' and import updates to the list from table 'tmp_DBase_Editor'. If an item is no longer in inventory it is deleted first from tmp_DBase_Editor and then updated in BookList by the following query:

update BookList
set quantity = 0
where not exists (select * from tmp_DBase_Editor where BookList .sku=tmp_DBase_Editor.sku)
GO

At a later point I delete all records from BookList that have a quantity of 0. This only works when I update the entire inventory, which has now grown to the point I want to be able to do partial updates. Can I set a range in BookList.price to import the updates, i.e. something along the lines of:

update BookList
set quantity = 0
where BookList.price >= [low price range] and where BookList.price <=[high price range]
where not exists (select * from tmp_DBase_Editor where BookList .sku=tmp_DBase_Editor.sku)
GO
0
bpfsr
Asked:
bpfsr
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I don't see a problem with the syntax.
0
 
reb73Commented:
Two wheres in the last update statement?

Should be -

update BookList
set quantity = 0
where BookList.price >= [low price range] and where BookList.price <=[high price range]
and not exists (select * from tmp_DBase_Editor where BookList .sku=tmp_DBase_Editor.sku)

But apart from this small correction (second where changed to and), your query looks fine as angelIII has indicated..

(No points necessary!)
0
 
reb73Commented:
Actually there's an extra where that I missed out as well ;-)

update BookList
set quantity = 0
where BookList.price >= [low price range] and BookList.price <=[high price range]
and not exists (select * from tmp_DBase_Editor where BookList .sku=tmp_DBase_Editor.sku)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now