May 6, 2008 at 7:56 am
hi guys, I hope you can help me. I have a list of items and prices from an excel spreadsheet. Using the concaneting method i have put the columns with '' to be able to use the update method.
this is an example of my list that i need to update
ItemNumber Price
Item1 33.09
Item2 34.00
item3 35.00
Using concaneting i have the rows as ('item1','item2','item3')
and for the Price column ('33.09', '34.00','35.00')
this list is over 5000 rows. can you help me with the update statement please?
May 6, 2008 at 8:33 am
I'm not clear on what you're trying to update. I see that you have items and prices, but I don't see what you're changing them from/to. Can you clarify a little bit, please?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 6, 2008 at 8:39 am
sorry i wasn't clear. I have Item1, item2, item3 already in my table. I need to update the existing prices. 😀
May 6, 2008 at 8:48 am
Sorry, you're still not clear. What do you want to update the prices to? Do you want to update the price in all 5000 rows, or just those that meet certain criteria?
John
May 6, 2008 at 8:55 am
at this time i have this in current table:
ItemNumber Price
Item1 22.09
Item2 15.09
item3 0.00
item4......so on
I need to update the price columm to:
ItemNumber Price
Item1 33.09
Item2 34.00
item3 35.00
item4....
Update tblItems
set Price= ?
where ItemNumbers in ('Item1', 'Item2', 'Item3',....)
that where i got stock... :ermm:
May 6, 2008 at 8:56 am
DBA (5/6/2008)
sorry i wasn't clear. I have Item1, item2, item3 already in my table. I need to update the existing prices. 😀
Assuming the prices are in the Excel spreadsheet, you have a couple of options.
OpenRowset will allow you to query the spreadsheet, in which case you can use that in the From clause of an update statement.
Alternately, you can import the spreadsheet into a table, use that table for your updates, and then drop that table.
If you will be doing this regularly (as opposed to once), you might be able to set up the spreadsheet as a linked server, and then use that for your update.
Take a look at OpenRowset and linked servers in Books Online, see if those will do what you need. If that doesn't clarify it enough, ask here on specifics.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 6, 2008 at 9:17 am
Hi,
I guess you have a Table with item prices and want to update these with values from a Excel file.
If so, you can simply access the Excel-File with the OPENROWSET feature. To use this, AdHoc Queries have to be enabled in the Serverconfiguration.
The Update Statement could be like:
UPDATE <ItemPriceList>
SET <Price> = xlsData.<NewPrice>
FROM <ItemPriceList> a INNER JOIN OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\book1.xls', [Sheet1$]) xlsData
on a.<ItemNo> = xlsData.<ItemNo>
Wolfgang Lengenfelder
May 6, 2008 at 10:29 am
Thanks Wolfgang Lengenfelder. worked like a charm.:)
May 6, 2008 at 10:40 am
I have to do this update in a server(production) where adhoc queries are not allowed, i also tried doing it by creating a table in my development server with the information i need to update but since my server is not a linked server it does not let me do the update. Is there another way? can i do it using an ssis package? or a tsql statement?
May 6, 2008 at 11:21 am
You can open MS Access, create new database and link two tables - one from excel spreadsheet and one from SQL Server.
then you can create new query and run update on SQL table.
The syntax for update in Access is a bit different.
update dbo_sql_table as a inner join Sheet1 as b on a.myid = b.F2
set a.price_field = 34.32
where a.myid = 11
Heh I just tried it and it works 🙂
Piotr
...and your only reply is slàinte mhath
May 7, 2008 at 7:54 am
yes i did it through access and it worked!!!! thank you!!!!! 🙂
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply