[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Update within a price range in SQL Server

Posted on 2009-04-20
3
Medium Priority
?
316 Views
Last Modified: 2012-08-14
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
Comment
Question by:bpfsr
  • 2
3 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24186734
I don't see a problem with the syntax.
0
 
LVL 25

Expert Comment

by:reb73
ID: 24186864
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
 
LVL 25

Accepted Solution

by:
reb73 earned 2000 total points
ID: 24186875
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

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Suggested Courses

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question