Does this SQL Statement Look Safe to Run?

I need to update reorder and restock based on the mpq listed in the supplier 
table.

I wrote a select to check:
select item.description, item.itemlookup code, item.restocklevel, 
item.oreorderpoint, item.id, supplierlist.itemid, 
supplierlist.masterpackquantity
from supplierlist where item.id = supplierlist.itemid

then I wrote an update:
update item set item.reorderpoint = 1 from supplierlist where 
supplierlist.masterpackquantity <=1 and item.id=supplierlist.itemid

I ran through several permutations of this query to get the results I wanted 
on an offline db, and everything looks good, but thought I'd ask - am I 
missing anything?

Just checking...
0
Chris6982 (632)
9/1/2009 11:44:01 PM
pos 14173 articles. 0 followers. Follow

1 Replies
744 Views

Similar Articles

[PageSpeed] 55

Your select statement shoul look like this:

select item.description, item.itemlookupcode, item.restocklevel, 
item.reorderpoint, item.id, supplierlist.itemid, 
supplierlist.masterpackquantity
from item join supplierlist on item.id=supplierlist.itemid 
and item.supplierid=supplierlist.supplierid 

Your update query should look like this:

update item set item.reorderpoint = 1 from item 
join supplierlist on item.id=supplierlist.itemid
and item.supplierid=supplierlist.supplierid 
where supplierlist.masterpackquantity <=1 

From your logic you presented, it appears that you are working on a store 
database or a single store operation, and that you want to change all the 
reorder points to 1 where master pack quantity is 1 or 0.  If this is not the 
case, please specify.

"Chris" wrote:

> I need to update reorder and restock based on the mpq listed in the supplier 
> table.
> 
> I wrote a select to check:
> select item.description, item.itemlookup code, item.restocklevel, 
> item.oreorderpoint, item.id, supplierlist.itemid, 
> supplierlist.masterpackquantity
> from supplierlist where item.id = supplierlist.itemid
> 
> then I wrote an update:
> update item set item.reorderpoint = 1 from supplierlist where 
> supplierlist.masterpackquantity <=1 and item.id=supplierlist.itemid
> 
> I ran through several permutations of this query to get the results I wanted 
> on an offline db, and everything looks good, but thought I'd ask - am I 
> missing anything?
> 
> Just checking...
0
MattHurst (182)
9/2/2009 2:27:01 AM
Reply:

Similar Artilces:

Excel 2003 running on XP
I have a column of numbers that represent the dyas of a reservation. The column has a subtotal for the month for the total days rneted that month. Now year-end, I would like to know the total numbers of night rented for the year. Is there an easy way to get that (maybe add all the subtotals together?) without having to create this calculation by hand (like writing =b12+b17+b23...). I have several spreadsheets to make this calc in and would like it to be accurate and easy! Thank you for your help! Why dont you post examples of how your data is arranged..? -- Jacob &...

IF,Then statement?
Hi All I am trying to find a way to write and if/then statement. I have a column(A) with gas stations (MoBil, Citgo, Etc) next to it I have a column (B) of gas MPG. I want to average each type of gas. So I was thinking of adding a column (C) for each type of gas. If A#=C(header), THEN place B# in C#. I can then take and average the column. I just don't know how to write it? OR is there a simplier way to write it? Thanks Bill -- bhowe ------------------------------------------------------------------------ bhowe's Profile: http://www.excelforum.com/member.php?action=getinfo&user...

Look up easier emails
when wanting to quickly see the emails in History, it is as of now still necessary to click, open up etc and a little dreadful. Is there a way to click for example on the space bar and to get a quick preview of the content of the emails. Now it takes a while. Although I like the history function very much it would add some value right? Thanks in advance! Wonder if some of you are experiencing the same. At the left hand side of each email in the grid view there is a small blue triangle pointing right. Click this to open the 'Preview' view of the respective email. You can also cus...

Scheduled tasks dont work but look like they are
Hi All, We are having intermittent problems with Windows Task Scheduler. Two scenarios: 1. Everything will work fine for a few weeks, then suddenly tasks that used to work ok will complete as normal, but task scheduler does not know they are finished, and so the tasks status sits as running.. even though the program and executable have closed and gone away.. this stops future instances of the scheduled task from running. 2. Tasks that are scheduled to run, and have run for weeks without issue, suddenly just stop running. They sit waiting for their scheduled start time, the ...

Need help with SQL tables
After running a security reconcile sa was no longer a user in GP. I received the answer below about re-adding it, but I don't know how to add a userid in to a table. I can't find anything on knowledgebase about this. We don't have the SQL Enterprise Manager but use the SQL 2005 Management Studio Express. Can anyone help me? I just need to know the script that will add it to these tables. Thanks. Tracey D "Therefore it will be just a matter of adding 'sa' USERID into the following tables in DYNAMICS database as a minimum. Once added, use ano...

Run-time error '5'
Hello, I'm getting a weird error on one of our computers. As soon as I open Excel and click anywhere on the sheet, I get an error: Run-time error '5' Invalid procedure or call argument This is just a blank document, with no macros or VBA code. Why am I getting it? I would appreciate your help. Thank you, -- Peter Afonin Hi try removing all add-ins -- Regards Frank Kabel Frankfurt, Germany Peter Afonin wrote: > Hello, > > I'm getting a weird error on one of our computers. As soon as I open > Excel and click anywhere on the sheet, I get an error: > ...

Installation Problems SQL 2005
I keep getting the following Error when installling CRM 3.0 Small Business Edition "Service mssearch was not found on computer 'SERVER1'. The specified service does not exist as an installed service" I have the Microsoft Full-Text Engine for SQL Server Service Installed and running. Can anyone help? Are you runndng your domain on a Windows Small Business Server? -- Rgds Michael MCDBA 2000 | MCITP DBA 2005 "jl815" wrote: > I keep getting the following Error when installling CRM 3.0 Small Business > Edition "Service mssearch was not found on com...

Using Float Type Variables for Primary on SQL 2005
We seem to have hit an oddity. We have a stored procedure that gets the next sequence number. It is protected by a transaction so no two processes could update the table of ids at the same time. Every so often, we get duplicates. How could that happen? Is the value based on mantissa? If so, is there a way to expanded it? Keep in mind, I can't change the type from float. Any help is greatly appreciated. Thanks in advance On 2010-07-19 16:19, RG wrote: > We seem to have hit an oddity. > Please don't multi-post. If you have to post to multiple newsgroup...

CPU spikes on sql server
We have noticed most of the same performance issues that are reported around here, but in trying to research a few of these I noticed the following strange behavior. Every 10 minutes a sql agent runs a process for "stored procedure priming". When this runs, the cpu on the server pegs at 100% for about 30 seconds. then back to 0% or so until the next firing This is a almost brand new install and only 1 or 2 users hit it. We have only begun to put data into it, so there isn't very much. Seems odd to me that a frequently scheduled process would hit things that hard that ...

Change IP address in Excel Sheet for SQL sever
We have change the IP address of our SQL Sever from 192.168.0.xxx to 192.168.0.4. This results in big problems as all our Excel Sheets with Pivots and SQL Queries in the have stopped working. Cause is that instead of using name we have used IP address in our ODBC set-up so the Excel file has the IP insted of the name of the SQL server. Now, one way to solve this is to save every sheet as XML and then change the adress, then import it and save it as an xls file again - losing graphs etc. Is there anyone that have an alternate solution to this problem and I will be eternaly greatful -...

Running Access 2003 db in 2007, and a few forms don't work
I have a database that's been updated from at least Access 97 (maybe Access 2, I can't remember anymore) and has been running fine in Access 2003 for years. My client upgraded to Vista with Office 2007. Almost everything in the database works fine, except 3 forms. Each of these forms has a tab control. Two of the forms have subforms on some of the tabs, one doesn't. When I try to open the forms in Access 2007 running on Vista in Virtual PC, I get the error "There isn't enough memory to perform this operation. Close unneeded programs and try the operation again."...

Help looking up the last product in a container
I have a data base that tells production what porduct to put in what trailer and I need to be able to find out what the last product was in the trailer. All this information is in the table on the last trailer by product name, trailer number and date returned. Can someone help me with this? -- Roger Perkins rperk60@swbell.net Use DLookup() to get the previous product. This example assumes a table named tblLoad, with fields like this: TrailerID Number what trailer is used for the load. ProductID Number what product was carried. LoadDate Date/Time when this...

Does ShellExecute run in separate thread?
How can I wait for ShellExecute command. I believe that it spawns a thread. Is there any way to synchronize that? rgds subhash Use ShellExecuteEx() or CreateProcess() { +WaitForSingleObject() } if you want to wait on the process that it runs. -- Cheers Check Abdoul [VC++ MVP] ----------------------------------- "Subhash Badri" <subhash.badri@in.bosch.com> wrote in message news:bog6ug$9qv$1@ns2.fe.internet.bosch.com... > How can I wait for ShellExecute command. I believe that it spawns a thread. > Is there any way to synchronize that? > > rgds > subhash...

How often should i run isinteg / defrag
Hello, was wondering how often is it recommended to run isinteg / defrag i guess that in defrag it depends on what i've been doing with the IS, but what a healty schedule for isinteg. TIA :) As a rule of thumb I would say only when required. Unless logfiles or error messages point to a condition where running the utility is required I would not run it. Unless you have a problem why chance a further problem or the down time for users. There is no requirement that I am aware of for this utility to be run on a regular basis. "ZeroLeveL" <zerolevel@softhome.net> wrote i...

Field with running count of records
Hi, I have a query with fields such as date, Item Number and Quantity. I want to create an additional field called Count that will act like an autonumber. It will assign a value of 1 for the first record, 2 for the second number etc. Can someone tell me how to do this? Thanks, -- Chuck W There are tons of posts on how to create a "ranking" query. You must have a field or fields that uniquely identify the sort order. If you provided significant table and field names as well as your desired sort order, someone could create the SQL for you if you can't search for and fi...

Installing Problem SQL SP3a
Hello NG, If got a new W2K3-Server with installed AD and SQL2000 with SP3a. The machine works properly. On installing CRM1.2 the following error occurs: 'The specified SQL-Server must be running Microsoft SQL-Server version 2000 SP3 or later.' Please help me. Thanks, Andreas. ...

SQL to get the lowest unique value from table
Hi I'm looking for some SQL to get the lowest unique value in a table: eg, <table> ID BID 1 1 2 1 3 1 4 2 5 2 6 2 7 3 8 4 3 is the lowest unique value. I would need to return the ID. Any suggestions? thanks Lee "Lee" <lee@digital-interactive.com> wrote in message news:a981d263-7f22-4b14-8493-014559da8590@z41g2000yqz.googlegroups.com... > Hi > I'm looking for some SQL to get the lowest unique value in a table: > eg, > > <table> > > ID BID > 1 1 > 2 1 > 3 1 > 4 2 > 5 2 > 6 2 ...

Use Stored Procedure developed in C# in a SQL Query
I created a Stored Procedure by selecting a SQL Server Project in Visual Studio 2008 using C#. It receives a SqlString as a parameter and return how many times a specific character was found. I deployed the procedure to a SQL Server database and can see it listed under the database. One caveat is that it has a small lock icon on it. I have a select query where I want to use the stored procedure. I am thinking this is doable in a select query but can't seem to get it to work. What I want to do is to have a select statement listing some fields and one of the fields being th...

Broken statement import
Hi, Rather foolishly, it now turns out, I downloaded and installed Microsoft Office Accounting Express 2008 for evaluation purposes. Now, Money 2005 (14.0.120.1105) running on 64-bit Vista Business no longer downloads and imports bank statements automatically. Previously, I would do the following: 1) Load Money (obviously) and open my bank .mny file 2) Click Favourites, Favourite Web Sites, Your Banks, Barclays Bank plc, Web Statements Logon Page 3) Enter login credentials Then I would download the latest transactions, and Money would import them into my accounts directly. Now, it n...

Conditional Formating (If statements)
In the conditional formatting box how would I make B4 turn yellow if F12 is between 10 and 15? Right-click Sheet, click 'View Code', and paste this into the window that opens: Private Sub Worksheet_Change(ByVal Target As Range) Dim rnArea As Range Dim rnCell As Range With rnCell If Range("F12").Value >= 10 And Range("F12").Value <= 15 Then Range("B4").Interior.ColorIndex = 6 Else: Range("B4").Interior.ColorIndex = 0 End If End With End Sub -- Ryan--- If this in...

Simple Format for Customer Statement
Hello All Is there available another format for printing customer statements? I'm mostly interested in a format that shows charges and payments without all the details and allocation to invoices. In other words I need a statement is the old fashion simple (Debit, Credit, Balance) format. Thank you Thanks Morris, I'll try it out. "Morris Paint" wrote: > This is our current statement. We are having another one written that shows > current period transactions in one area and all open invoices in another. > > "Mario" <Mario@discussions.microsoft...

Looking for book on Exchange 2010
We currently have an Exchange 2007 environment with about 5000 users. We're looking at migrating to Exchange 2010, probably in the next couple of months. I am looking for recommendations for a book on Exchange 2010. Some of our main areas are in data redundancy and migration from 2007. The Jim McBee and David Elfassy book, Mastering Exchange Server 2010 was the one I was looking for first (we've used the "Mastering.." series in the past), but it looks like it's not going to be released until April. I came across the "Exchange 2010 Unleashed"...

No Standby in Safe Mode?
I've had an on-going frustration with my laptop (Dell D600 Latitude running XP Pro SP 3)- there are many times I use it that I do not want my WiFi network services or "protection programs" (AVG free, Spyware terminator, ZoneAlarm) loaded. I'm going to sit at home and dink a bit, or use a VB program I wrote at church. The church scenario is especially bad. The protection prgrams want to touch every file and scan it, periodically call home for updates, run whole computer scans, etc. Really slows the machine down. So I would start it up at home, wait for it to fini...

SQL Command match Items to Matrix
SQL command that will put all items which contain a certain Supplier ID # to be added to a matrix that I have created. We have a company and we want to do a matrix for all of there items. But instead of manually adding each item to the matrix. If I could run an SQL command that will take all items with the supplier ID attached to them and have them added to the matrix. I am aware that I will still have to label the dimensions. It would just be fantastic if I could do a mass add. Hopefully someone has a creative mind. I tried some commands, but I am not an SQL master either. I know that ...

Upsize AutoNumber field to SQL Server
The Upsizing Wizard Add-In in Access 97 would automatically create an Insert Trigger when upsizing to SQL Server. But when I use the built-in Upsizing Wizard in Access XP (2002), it doesn't seem to create those triggers. Is there an option I'm missing somewhere or will I have to manually create those triggers for my upsized DB? TIA! ...