0

I've got a query that I ran against a table of stock items to change a field from false to true so that a label would be printed out as per the below.

UPDATE [StockDB].[dbo].[StockItem] SET [UseDescriptionOnDocs] = 0 Where [UseDescriptionOnDocs] = 1

I have since been told that all of the items do not need this and to only do it for the ones in a list of 1150. From that I need to modify the query, something which I am really struggling with!, so it can only do it per item which are in the format of R368/WASHER/M4/ZINC/PL etc. I thought I may be able to do it using LIKE but it will only do one line? If I could get it to query a list from Excel or CSV that would be great.

Any pointers would be good as I'm an absolute novice with this once you get past the simple stuff like the above!

Thank you.

Andy J
  • 1
  • I try to recapitulate: You have a table _StockItem_. In this table you want to update the column _UseDescriptionDocs_ from _1_ to _0_ for items which you determine by another list of 1150 items which fulfill specific conditions. Is that right? – schlonzo Jul 09 '14 at 10:53
  • Yep that's exactly what I need to do. Sorry for the poor description! – Andy J Jul 09 '14 at 11:01
  • OK, and where is the other list located? Is it also stored in the database? It would be helpful if you describe the definition of your database tables. – schlonzo Jul 09 '14 at 11:03
  • The list is of StockItem's as per the above R368/WASHER, that's a column in the table and UseDescriptionDocs is another column in there. – Andy J Jul 09 '14 at 11:08
  • I've got the list of the ones I need to update on a separate CSV as well. – Andy J Jul 09 '14 at 11:10
  • If you want to use data of a CSV file in a sql query, you first have to import the csv file to a (temporary) table in your database. Are you using MySql, MS SQL or something else? – schlonzo Jul 09 '14 at 11:13
  • MS SQL I'm using. I had read about importing the CSV and stopped there as I was thinking it may break something! – Andy J Jul 09 '14 at 12:36
  • I see what you are saying now though. If I can get it into SQL as a temp table then I get the query to reference that table for its information and amend the main one. – Andy J Jul 09 '14 at 12:38
  • Yes that's right. You could use the SQL Server Import and Export Wizard. You should find it under _Start -> Programs -> -> Import and Export Data_ – schlonzo Jul 09 '14 at 12:43
  • Got the data in, now the struggle on how to get the new table referenced into the query. Any pointers? – Andy J Jul 09 '14 at 13:20
  • What is the key of your _StockItem_ table? Is this key also contained in your new table with the imported data? If so, then you can do something like `SELECT * FROM StockItem JOIN ON StockItem. = . WHERE ...` – schlonzo Jul 09 '14 at 13:25
  • The key I think is PK_StockItem, and as far as I can see it's not no. I've very little SQL experience I'm afraid its kind of been dumped on me. – Andy J Jul 09 '14 at 13:28
  • To realize what you want, you must have criteria in your imported data, which identifies a single item in your StockItem table. – schlonzo Jul 09 '14 at 13:45

1 Answers1

0

With MySQL (and others) you can load data from a file to a table. See http://dev.mysql.com/doc/refman/5.1/en/load-data.html to understand and for exemple.

Once you have your items loaded into a SQL table, you just have to do an update + join query. See http://dev.mysql.com/doc/refman/5.1/en/update.html to understand and How can I do an UPDATE statement with JOIN in SQL? for exemples.

Community
  • 1
  • 1
DylannCordel
  • 586
  • 5
  • 10