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.
| Author |
Topic |
|
mcampos
Starting Member
2 Posts |
 Posted - 2006-08-10 : 16:45:21
|
| I am fairly new to SQL programming and would like to see if you can provide me with some direction on a small project I would like to accomplish. I have experience working with databases (mainly in with different versions of FoxPro) and reporting with Crystal reports, etc. but not much with SQL.The project that I would like to work on has 2 SQL DBs and entails looking in a database for empty CLASSCODE fields, populate these fields from a “storage” database that contains un-used CLASSCODE codes and delete the assigned codes from the storage DB or mark them as used possibly the book code.The main DB called books has the following relevant fields, bookid, bookname, CLASSCODE (besides a number of other fields which are irrelevant to what I need to do). The “storage DB called classcode has 3 fields, CLASSCODE, Used (single character intended to contain either a Y or NULL) and date assigned. The original idea was to put a Y in the used field and insert the date the code was assigned to a book, but this is not entirely necessary, we have 90k unused codes and it really does not matter if they are simply deleted from the DB.My idea is to create a script to accomplish this that I can run periodically when new books have been added to the DB. I can setup the update routine (something like the list below) but what I am stuck with is trying to do the following:Pick the first record (classcode) from the classcode DBGo to the books DB and update it with this classcodeGo back to the classcode DB and mark the used record with a Y in the Used fieldLoop though the books DB and perform the same operation until no more records exist with an empty classcode field.I will greatly appreciate your feedback you can provide.Mike.UPDATE [dbo].[Books]SET dbo.Books.Classcode = [dbo].[classcode].[classcode]From dbo.classcode On [dbo].[Books].[classcode]= [NULL]Populate DB field from list in “storage” DBMike Campos - CA |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-08-10 : 16:58:20
|
| Here's what the update statement should look like:UPDATE bSET Classcode = c.classcodeFROM dbo.Books bINNER JOIN classcode cON b.PKColumn = c.FKColumn (or vice versa) WHERE b.classcode IS NULLYou'll need to post DDL for your table (CREATE TABLE statements), INSERT INTO statements for sample data for both tables, and the expected result set using that sample data. This way we can work on your problem on our machines and develop a solution for you. When I post a problem, I typically provide 5-10 rows per table that is impacted by my problem. Here's an example of one of my posts:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=42516Notice how quickly I got an answer for a complex question.Tara Kizer |
 |
|
|
mcampos
Starting Member
2 Posts |
Posted - 2006-08-11 : 10:48:59
|
| Thanks for your reply Tara,I will try to add some of the data below to see if I can explain what I am trying to do without complicating it too much:dbo.BooksBookid ClasscodeA1234 00001B1234 00002C1234 00003D1234E1234dbo.classcodeclasscode Used Date00001 Y 5/17/2006 12:00:00 AM 00002 Y 5/17/2006 12:00:00 AM 00003 Y 5/17/2006 12:00:00 AM 0000400005000060000700008I need to pick the next classcode.classcode record that does not have a Y in the Used field and apply it to the first books.classcode record that has a NULL classcode record and then mark the used classcode.classcode record with a "Y"Hopefully this will make some sense, I will greatly appreciate any feedback.Mike.Mike Campos - CA |
|
|
|
|
|
|
|
|
Populate DB field from list in “storage” DB