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

Use SQL Update and set to change 12,000 + records

Hi...Looking for a sanity check (or better option) to use SQL update and set commands to change 12,000+ records in a table.

Here is a sample:
Update IV10301 SET COUNTEDQTY=0,VARIANCEQTY=0, where ITEMNMBR= '999005113'
Update IV10301 SET COUNTEDQTY=0,VARIANCEQTY=0, where ITEMNMBR= '999005114'
Update IV10301 SET COUNTEDQTY=0,VARIANCEQTY=0, where ITEMNMBR= '999005115'
Update IV10301 SET COUNTEDQTY=0,VARIANCEQTY=0, where ITEMNMBR= '999005116'
Update IV10301 SET COUNTEDQTY=0,VARIANCEQTY=0, where ITEMNMBR= '999005117'

When running this sql script, it craps out on syntax...
wondering if I can declare the "Update IV10301" and then simply list the "SET" for each record?
if so, what is correct syntax?

Also...when executing this statement, SQL server indents every update/set. Is this normal?

Not in love with this method, so if anybody has better option...happy to listen.

Data to SET is contained in a spreadsheet that I am using Concatenates to extract and modify correct syntax if that background helps.
0
KevinHatt
Asked:
KevinHatt
  • 5
  • 4
  • 3
  • +1
2 Solutions
 
sm394Commented:
Update TableName
SET COUNTEDQTY=0,VARIANCEQTY=0
where ITEMNMBR in( '999005113',
 '999005114',
'999005115',
 '999005116',
 '999005117')
0
 
8080_DiverCommented:
You need to learn about the CASE statement. ;-)
 

Update IV10301 
SET COUNTEDQTY = CASE WHEN ITEMNMBR= '999005113' THEN 0
                      WHEN ITEMNMBR= '999005114' THEN 0
                      WHEN ITEMNMBR= '999005115' THEN 0
                      WHEN ITEMNMBR= '999005116' THEN 0
                      WHEN ITEMNMBR= '999005117' THEN 0
                      ELSE COUNTEDQTY
                 END,
    VARIANCEQTY = CASE WHEN ITEMNMBR= '999005113' THEN 0
                       WHEN ITEMNMBR= '999005114' THEN 0
                       WHEN ITEMNMBR= '999005115' THEN 0
                       WHEN ITEMNMBR= '999005116' THEN 0
                       WHEN ITEMNMBR= '999005117' THEN 0 
                       ELSE VARIANCEQTY 
                  END;

Open in new window

0
 
KevinHattAuthor Commented:
Hmmm...I guess my example can lead to false conclusions...
The COUNTEDQTY AND VARIANCEQTY cannot be assumed to always be 0. It is an actual count result.
Apologies for the misdirection
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
8080_DiverCommented:
Indeed, your example did lead to "false conclusions".  Do you mean that you want to update the COUNTEDQTY and VARIANCEQTY  to the appropriate counts for those ITEMNMBR values?
If not, where do the values come from?

UPDATE I
SET COUNTEDQTY = Z.COUNTEDQTY,
    VARIANCEQTY = Z.VARIANCEQTY
FROM IV10301  
INNER JOIN
(
 SELECT ITEMNMBR, 
        0 + COUNT(whateveryou count) AS COUNTEDQTY, 
        0 + COUNT(wheteverelseyoucount) AS VARIANCEQTY
 FROM IV10301 
 WHERE ITEMNMBR IN ( '999005113',
                     '999005114',
                     '999005115',
                     '999005116',
                     '999005117'
                   )
) Z
ON Z.ITEMNMBR = I.ITEMNMBR;

Open in new window

0
 
KevinHattAuthor Commented:
Hi 8080 Diver,
You know what they say about a representative data set...it aint so representative ;-)

I think I can use your example:

Update IV10301
SET COUNTEDQTY = CASE WHEN ITEMNMBR= '999005113' THEN 0
                      WHEN ITEMNMBR= '999005114' THEN 0
                      WHEN ITEMNMBR= '999005115' THEN 0
                      WHEN ITEMNMBR= '999005116' THEN 0
                      WHEN ITEMNMBR= '999005117' THEN 0
                      ELSE COUNTEDQTY
                 END,
as the actual CountedQty and Variance QTY are in a spreadsheet. A simple concatenation of those cells seems to be giving me the results of 10 records so far.

Next issue is a "stack overflow error" when checking syntax...suggested simplifying the query...
May have to do it in manageable chunks to get through all 12,000+ records

Any thoghts on that?

Kevin

0
 
8080_DiverCommented:
the actual CountedQty and Variance QTY are in a spreadsheet
Are the ItemNmbr's in the spreadsheet, too?
If so, import the data into a work table in the database and then you can do a fairly simple UPDATE . . . WHEN I.ITEMNMBR =  S.ItemNmber.
 

UPDATE I
SET COUNTEDQTY = S.COUNTEDQTY,
    VARIANCEQTY = S.VARIANCEQTY
FROM IV10301  
INNER JOIN StagingTable S
ON I.ITEMNMBR = S.ITEMNMBR
WHERE I.ITEMNMBR IN ( '999005113',
                     '999005114',
                     '999005115',
                     '999005116',
                     '999005117'
                   );

Open in new window

0
 
8080_DiverCommented:
Oops, forgot an alias. ;-)
UPDATE I
SET COUNTEDQTY = S.COUNTEDQTY,
    VARIANCEQTY = S.VARIANCEQTY
FROM IV10301 I
INNER JOIN StagingTable S
ON I.ITEMNMBR = S.ITEMNMBR
WHERE I.ITEMNMBR IN ( '999005113',
                     '999005114',
                     '999005115',
                     '999005116',
                     '999005117'
                   );

Open in new window

0
 
Chris LuttrellSenior Database ArchitectCommented:
Can you do a dump of ITEMNMBR, COUNTEDQTY, VARIANCEQTY into a temp table?  Then use an update like below to update your base table from the temp table.  You can drop the temp table after the operation.
That is the most efficient way to load a lot of data from a spread sheet.  I do it all the time.
Your Stack Overflow is coming from trying to build too large of a string in the update statement.
Update IV10301 
SET COUNTEDQTY=TempTab.COUNTEDQTY, VARIANCEQTY=TempTab.VARIANCEQTY
From TempTab
INNER JOIN IV10301 on IV10301.ITEMNMBR = TempTab.ITEMNMBR

Open in new window

0
 
KevinHattAuthor Commented:
Hi CG,

Yeah...looks like the temp table method is best course of action...stack overflow is killing me.
Can you offer quick advice on dumping data into a temp table from Excel?
0
 
Chris LuttrellSenior Database ArchitectCommented:
Can you use Management Studio import wizard? That is how I do one time loads.  
0
 
KevinHattAuthor Commented:
I went old-school on it and used Access to import the SS, link the table, then an update query to change the required fields...
Would you mind if I split the points between you and 8080 diver?
His/her suggestion was along the same lines of using a temp table which led to my solution

Thanks for the help...always nice to pick the brains of the gurus :-)
0
 
Chris LuttrellSenior Database ArchitectCommented:
Sound good to me.  Glad you solved your problem.
0
 
8080_DiverCommented:
Sounds right to me, too.  
For the record, 8080_Diver is male and the 8080_Diver name comes from SCUBA Diving with 80ft visibility and 80F water temp (which is where I have been wishing I was all week! ;-).
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 5
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now