Mass Update Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Mass Update Query

Author

Topic

thomasr79
Starting Member

3 Posts

Posted - 2008-01-31 : 12:28:07

Hi All,

I don't know sql well enough to know the answer to this so hope you can all help.

I have a table in question which contains approx 20 columns and 90,000 rows. I want to update just 1 column of data based on the entry in ColumnA.

So, I have an excel spreadsheet which looks like ( : represents column split)

Item-Code1:20
Item-Code2:40
Item-Code3:10

The two colums of data above are in columns called:

ItemCode:StockLevel

As an example of an update based on row 1 - I want to run an update which says:

Look for Item-Code1 in ItemCode and Update StockLevel to 20. Because there will up to 90,000 of these updates I need it to scroll through.

Previously I have used the sql import wizard. Hope you can help, need an answer urgently!

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2008-01-31 : 13:48:23

Something like this might help:


Update YourTable 
Set Stocklevel = substring( @Item , charindex(':', @Item)+1, len(@Item) )
Where ItemCode = Left(@Item,charindex(':', @Item)-1)

Run a SELECT instead of an update and see the values first before doing the actual update.


Select NewStocklevel = substring( @Item , charindex(':', @Item)+1, len(@Item) )
	,NewItemCode = Left(@Item,charindex(':', @Item)-1)
FROM yourTable

You can get the data from excel sheet into some temp table and run above queries on it..run your updates also on temp tables and when the data looks good only then you can join the temp table with original table and do the update on the original table.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

thomasr79
Starting Member

3 Posts

Posted - 2008-02-02 : 09:00:45

hi,

thanks for the reply. I have got the data in sql table now. Was hoping the code could be clarified for us:

Table Temp.ShippingUpdate Contains:
CustomerCode, CustomerName,Address1,City,Country,PostalCode

Table Customer.ShipTo Contains (in different order to above and also contains more columns):

CustomerCode, CustomerName,Address1,City,Country,PostalCode

So, exactly the same column names.

We want to join tables then and do:

1) Lookup CustomerCode from Temp.ShippingUpdate IN Table Customer.ShipTo
2) Update (CustomerName,Address1,City,Country,PostalCode) FROM Temp.ShippingUpdate TO Customer.ShipTo

Could you help further please? Sorry if obvious from your example above.

We will make a full backup before we run any commands.

thomasr79
Starting Member

3 Posts

Posted - 2008-02-02 : 09:07:36

Sorry I just noticed that I posted the text from another update we need to do. Same princple because the previous entry of item code and stock levels, needs to also be done in the same fashion, i.e. same column names in both tables etc.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources