Avatar of bpfsr
bpfsrFlag for United States of America

asked on 

Help with change quantity in SQL Server

In my SQL Server database I have a list of items with a column for quantity. When an item sells, I want to reduce the quantity by 1. I import the sold items into a new table with:
create table DBase_Editor
(ItemID varchar (50) not null)

bulk insert DBase_Editor
from 'G:\DBase Editor.csv'
(firstrow = 2,
fieldterminator =',',
rowterminator = '\n')

Can somebody help me with a query that would reduce the Quantity column in table Vendor by 1 for each file in DBase_Editor by matching column 'ItemID' in table DBase_Editor with column 'identifier' in table Vendor? I then need a separate query that would on a second pass delete all records from table DBase_Editor where column 'quantity' is zero. Thank you.
Microsoft SQL Server 2005Microsoft SQL Server 2008Microsoft SQL Server

Avatar of undefined
Last Comment
Mark Wills
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

you cannot change the data while bulk inserting.
you have to load the data into a staging table, and then handle it from there.
Avatar of Mark Wills
Mark Wills
Flag of Australia image

you can create a format file (and might help with "9,999.00" - is this the same ?) and then use openrowset (bulk 'filename' etc...

that way, you get access to individual rows and columns (and could even do a replace(replace(price,'"',''),',','') killing two birds with one stone)
Avatar of Mark Wills
Mark Wills
Flag of Australia image

Blurred text
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Microsoft SQL Server
Microsoft SQL Server

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews


IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo