Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Updating a SQL Table with Criteria

Posted on 2011-02-10
32
Medium Priority
?
224 Views
Last Modified: 2012-08-13
Hi,

I have a table (ImportPCMCost) in SLQ that has 3 Columns, they are
1.) ID
2.) CPTCODE
3.) FEE

I need to update another table called Procedure_Fees, there are many columns in here but I need to update 2 based on the match from importPCMCost table the column named ID needs to match the columan in Procedure_Fees table named Procedure_Code_ID, I also need to update the field in Procedure_Fees (table) that is called Current_Effective_Date, with obviously the current date and Time.

Thank You
0
Comment
Question by:dmanisit
  • 18
  • 14
32 Comments
 
LVL 3

Expert Comment

by:jmro20
ID: 34864659
Update Procedure_Fees
Set Current_Effective_Date = GETDATE(),
      FieldToUpdate1 = i.CPTCODE,
      FieldToUpdate2 = i.FEE
From Procedure_Fees as f Inner Join
      ImportPCMCost as i On f.Procedure_Code_ID = i.ID
0
 

Author Comment

by:dmanisit
ID: 34864696
Thank you, can you explain what the as f and On f is in your statement. Im sorry I am really new to SQL

Here is what I have so far:

USE Ntier_Training

UPDATE PM.Procedure_Fees
SET Current_Effective_Date = GETDATE(),
      Current_Fee_Amount = dbo.ImportPCMCost.FEE,
      From PM.Procedure_Fees as f Inner Join
      ImportPCMCost as i On f.Procedure_Code_ID = i.ID
0
 
LVL 3

Expert Comment

by:jmro20
ID: 34864716
Thats an alias to the table, without alias it would be like this:
Update Procedure_Fees
Set Current_Effective_Date = GETDATE(),
      FieldToUpdate1 = ImportPCMCost.CPTCODE,
      FieldToUpdate2 = ImportPCMCost.FEE
From Procedure_Fees Inner Join
      ImportPCMCost On Procedure_Fees.Procedure_Code_ID = ImportPCMCost.ID
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 3

Expert Comment

by:jmro20
ID: 34864737
It's just to write less code!!, you can name it as whatever you want, in this case I used f for Fees and i for Import
0
 
LVL 3

Expert Comment

by:jmro20
ID: 34864769
This should do it:

USE Ntier_Training

UPDATE PM.Procedure_Fees
SET Current_Effective_Date = GETDATE(),
      Current_Fee_Amount =i.FEE,
From PM.Procedure_Fees as f Inner Join
      ImportPCMCost as i On f.Procedure_Code_ID = i.ID
0
 

Author Comment

by:dmanisit
ID: 34864770
Ahhh, i understand and thank you for explaining. So Here is my Query:

USE Ntier_Training
Update PM.Procedure_Fees
Set Current_Effective_Date = GETDATE(),
      Current_Fee_Amount = ImportPCMCost.FEE
   
From PM.Procedure_Fees Inner Join
      dbo.ImportPCMCost On PM.Procedure_Fees.Procedure_Code_ID = ImportPCMCost.ID

I executed and it says 0 rows affected?????
0
 
LVL 3

Expert Comment

by:jmro20
ID: 34864799
Try this first:
Select *
From PM.Procedure_Fees Inner Join
      dbo.ImportPCMCost On PM.Procedure_Fees.Procedure_Code_ID = ImportPCMCost.ID

And tell me if it returns any row.
0
 

Author Comment

by:dmanisit
ID: 34864806
Negative, no results
0
 
LVL 3

Expert Comment

by:jmro20
ID: 34864834
That means that the relationship of the table is either wrong or there are no records that match that columns value.
0
 
LVL 3

Expert Comment

by:jmro20
ID: 34864850
For example there are no records in Procedure_Fees that has a value in Procedure_Code_ID column = to any value in ImportPCMCost.ID
0
 

Author Comment

by:dmanisit
ID: 34864904
Ok, In Procedure_Fees, there is a column named Procedure_Code_ID (NO NULLS), and in Procedure_Fees, there is a Column named Procedure_Code_ID as well (NO NULLS), and in the table that I BUILT and loaded data into there is a column named ID, that is the same as Procedure_Code_ID
0
 

Author Comment

by:dmanisit
ID: 34864923
i am assuming that there is no relation in the table I built, how do I relate the ID field in the table I built to Procedure_Fee table?
0
 

Author Comment

by:dmanisit
ID: 34865223
Ok how about this? How about we scratch the idea of using the table called ImportPCMCost and we use a tab delimited file with the same info in it? Can you help there?
0
 
LVL 3

Expert Comment

by:jmro20
ID: 34865261
There's no need to create a relationship. You only need values in both tables to be the same.

Lets get some sample data. Could you please post here one record from Procedure_Fees table?
0
 
LVL 3

Expert Comment

by:jmro20
ID: 34865282
If you already have the data in a table, don't bother to use tab delimited file, it's easier from a table. We should get a look at the data itself to see if there are some reason to not getting any records when joinning the tables.
0
 

Author Comment

by:dmanisit
ID: 34865499
Procedure_Fees Data:
180      <Binary data>      NULL      175.8300      NULL      414      11      8      7/8/2010 11:42:51 AM
0
 

Author Comment

by:dmanisit
ID: 34865507
ImportPCMCost Table:

415      10022      206.00
0
 
LVL 3

Expert Comment

by:jmro20
ID: 34865529
Is the column Procedure_Code_ID the one with the value 414?

If this is true then, do you have a record in ImportPCMCost  table that has a value of 414 on the ID column?
0
 

Author Comment

by:dmanisit
ID: 34865540
Ok so I think I missed this earlier in your query. I think the query is incorrect, probably due to my explaining. So I will try it again.

In dbo.ImportPCMCost there are 3 columns:
Column 1: Procedure_Code_ID
Column 2: Procedure_Code
Column 3: FEE

I need to take the values that are in the table listed above and add these into PM.Procedure_Fees. Here is the part I think I left out, they DO NOT exist in this table yet, they only exisit in PM.Procedure_Codes. So I need to insert the records into PM.Procedure_Fees
0
 
LVL 3

Accepted Solution

by:
jmro20 earned 2000 total points
ID: 34865609
Ok, then you need something like this:

Insert Into Procedure_Fees      
(
      Procedure_Code_ID,
      Procedure_Code,
      FEE,
      Current_Effective_Date
)
Select
      ID,
      CPTCODE,
      FEE,
      GETDATE()
From ImportPCMCost
0
 

Author Comment

by:dmanisit
ID: 34866129
Ok, tried what you suggested that didnt work and also changed up what you have to this:

USE Ntier_Training

INSERT INTO PM.Procedure_Fees (Procedure_Code_ID,Current_Fee_Amount,Current_Effective_Date)

SELECT isnull(Procedure_Code_ID,'') as 'procedureCodeID', FEE, GETDATE() as 'dateTime' From Ntier_training.dbo.ImportPCMCost

And this is the error:

Msg 547, Level 16, State 0, Line 3
The INSERT statement conflicted with the FOREIGN KEY constraint "Procedure_Fees_Procedure_Code_ID_Procedure_Codes_Procedure_Code_ID". The conflict occurred in database "Ntier_Training", table "PM.Procedure_Codes", column 'Procedure_Code_ID'.
The statement has been terminated.

I gotta tell you. IM FRUSTRATED with myself. :-)
0
 
LVL 3

Expert Comment

by:jmro20
ID: 34866209
Thats because you are trying to insert a value that already exists in the Procedure_Code_ID column
0
 

Author Comment

by:dmanisit
ID: 34866221
Ok so I guess the question is, then how do I append to the current table?
0
 
LVL 3

Expert Comment

by:jmro20
ID: 34866234
Or you have table that has a foreing key with this table
0
 
LVL 3

Expert Comment

by:jmro20
ID: 34866296
From the error message I get that you also have a table named Procedure_Codes and this table has a foreing key with the Procedure_Fees table. The error is because you are trying to insert a record on Procedure_Fees table with a value in Procedure_Code_ID column that is not present in Procedure_Codes table.
0
 
LVL 3

Expert Comment

by:jmro20
ID: 34866303
You should insert to the Procedure_Codes table first and then Insert to Procedure_Fees table
0
 

Author Comment

by:dmanisit
ID: 34866397
Ok, so to keep you up to date here is where Im at:

USE Ntier_Training

SELECT * FROM PM.Procedure_Codes
WHERE Procedure_Code IN (SELECT cptcode FROM dbo.ImportProcedure_Codes)

i am just going to stuff the data that I need from this query in another table and then load that instead of updating Procedure_Codes first, due to the fact there is a ton of criteria to do that.

What do you think?
0
 
LVL 3

Expert Comment

by:jmro20
ID: 34866427
Thats ok
0
 
LVL 3

Expert Comment

by:jmro20
ID: 34867406
I think something like this should work:

USE Ntier_Training

INSERT INTO PM.Procedure_Fees (Procedure_Code_ID,Current_Fee_Amount,Current_Effective_Date)

SELECT isnull(Procedure_Code_ID,'') as 'procedureCodeID', FEE, GETDATE() as 'dateTime'
From Ntier_training.dbo.ImportPCMCos Inner Join PM.Procedure_Codes On Ntier_training.dbo.ImportPCMCos,Procedure_Code_ID = PM.Procedure_Codes.Procedure_Code_ID
0
 

Author Closing Comment

by:dmanisit
ID: 34872080
Thanks for all the help. Alot of the problem came from my lack of explaining the issue. I am still working on the solution as it has became more involved. I am opening a new thread that will explain the issues better. Sorry for the confusion
0
 
LVL 3

Expert Comment

by:jmro20
ID: 34872204
I saw your new question, and if really well explained. Now I understand your problem. Knowing that for sure I was going to help. Sorry for not getting it done but as you said the problem was "lack of explaining the issue"

Thanks for the points!!
0
 

Author Comment

by:dmanisit
ID: 34872220
No need to apologize, it was my ignorance. i was in a hurry and aggravated with SQL. I will take more time to better explain things in the future. Sorry about that
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

824 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question