Update a Column While in Query Mode 1

[MrArmstrong]

I have a table that I need to update one field based on another field . This is my Stock table for all the parts we stock. I need to go update the table reorder points. So a scenario would be where we have stock.Field 6 I need to change the reorder point.field to 4. Where we stock 100 I need to change it to 75.
The problem is we stock over 3000 parts so I have to run a query to pull all of the rows where we stock 6, once I have my data returned how do I update all rows to show 4.

Or is there a easier way to do this without querying on each single number?

 

[gmmastros]

To help clarify your question...

Please post some sample data from the table(s) involved.
Please post the query you are using to identify the records that need to be updated.

With this information it should be easier for us to help you.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[MrArmstrong]

Here are a few colums:
SELECT *,MAXQTYLEVEL AS Expr1
FROM STOCK
WHERE (MAXQTYLEVEL =6)

PART NUMBER DESCRIPTION MAXQTYLEVEL OPTQTYLEVEL
000-36689 TELEPHONE 6 0
01-P24481F001 POWER CONTROL PTRA200 6 0
01-P25390B002 SATCOM REMOTE CONTROL 6 0
01-P28870H002 LSTA-10 TRAY 6 0
01-P29106B002 PTPE-101 AMP 6 0
01-P29107B001 CONTR, PTPC-100 6 0
01-P32456A001 PWR SUPPLY FLTR, UHF 6 0
010131-311 BALL BEARING 6 0
014321-027 INDUCTOR 6 0
015-2020-106A CONTACT, PIN 6 0
015-2020-106C CONTACT, PIN 6 0
016-2020-139A CONTACT, SOCKET 6 0
016-2020-139C CONTACT, SOCKET 6 0
030-17700 MN WHEEL BRAKE 6 0
0341-0483-3 WINDOW SEAL 6 0
040-25300 LANDING GEAR WHEEL 6 0
047-5913-01 GRND STRAP F 6 0
047-6054-01 SPACER HF AN 6 0
052-0009-04 RESILIENT MO 6 0

In this case where all MAXQTYLEVEL are 6 I need to change all OPTQTYLEVEL to 4.

 

[gmmastros]

If I understand correctly, then this query should work for you.

[!]** Make sure you have a good backup before running this query[/!]

Update Stock
Set    OPTQTYLEVEL = 4
Where  MAXQTYLEVEL = 6

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[MrArmstrong]

Can I repeat this same command over again for each different number like Max= 2 OPT=1 and so on or will it fail. I want to find the easiest way to knock this out in one shot if possible.

 

[SQLSister]

YOu can write separate statements or consider using the CASE statment in your update.

"NOTHING is more important in a database than integrity." ESquared

 

[MrArmstrong]

Thanks I'll give it a try.

 

[MrArmstrong]

Successful thank you for your help.