Solved

Updating a SQL Table with Criteria

Posted on 2011-02-10
32
215 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
 
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

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 500 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

760 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now