Updating a SQL Table with Criteria

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
dmanisitAsked:
Who is Participating?
 
jmro20Commented:
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
 
jmro20Commented:
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
 
dmanisitAuthor Commented:
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
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
jmro20Commented:
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
 
jmro20Commented:
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
 
jmro20Commented:
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
 
dmanisitAuthor Commented:
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
 
jmro20Commented:
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
 
dmanisitAuthor Commented:
Negative, no results
0
 
jmro20Commented:
That means that the relationship of the table is either wrong or there are no records that match that columns value.
0
 
jmro20Commented:
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
 
dmanisitAuthor Commented:
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
 
dmanisitAuthor Commented:
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
 
dmanisitAuthor Commented:
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
 
jmro20Commented:
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
 
jmro20Commented:
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
 
dmanisitAuthor Commented:
Procedure_Fees Data:
180      <Binary data>      NULL      175.8300      NULL      414      11      8      7/8/2010 11:42:51 AM
0
 
dmanisitAuthor Commented:
ImportPCMCost Table:

415      10022      206.00
0
 
jmro20Commented:
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
 
dmanisitAuthor Commented:
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
 
dmanisitAuthor Commented:
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
 
jmro20Commented:
Thats because you are trying to insert a value that already exists in the Procedure_Code_ID column
0
 
dmanisitAuthor Commented:
Ok so I guess the question is, then how do I append to the current table?
0
 
jmro20Commented:
Or you have table that has a foreing key with this table
0
 
jmro20Commented:
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
 
jmro20Commented:
You should insert to the Procedure_Codes table first and then Insert to Procedure_Fees table
0
 
dmanisitAuthor Commented:
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
 
jmro20Commented:
Thats ok
0
 
jmro20Commented:
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
 
dmanisitAuthor Commented:
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
 
jmro20Commented:
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
 
dmanisitAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.