SQL INSERT or UPDATE Query Help

[TornierIT]

I am having difficulties wrapping my brain around this one. Still a newb when it comes to SQL. I created a table called "SURG_TYPE" that only has 2 fields (Surgery_Num, Surg_Type).

I have populated this table with the Surgery Numbers but now I need to populate the "Surg_Type" from a separate table (tblMeasurement).

The issue I am having is that there is a one to many relationship (Surgery_Type.Surgery_Num=tblMeasurement.srgID).

So I first need to check to see if the "Surg_Type" field is NULL, and if so, then add tblMeasurement.MeasurementType to the field.

Then the following criteria (sorry for the coding):

If Surg_Type in ('FinalRefraction', 'Refraction') then tblMeasurement.MeasurementType

else

If Surg_Type = 'LRI' and tblMeasurement.MeasurementType = "TORIC" then 'LRI+TORIC'

else

If Surg_Type = 'TORIC' and tblMeasurement.MeasurementType = "LRI" then 'LRI+TORIC'


So you can see sometimes you only need to evaulate the table that you are inserting into and sometime you need to evaluate the source and destination tables.

I am confused and I hope I didn't confuse you to. I tried to use CASE as well, but I think I am digging myself a deeper hole.

I know this is simple to a lot of you and can really use some help.

Thanks in advance

 

[jadams0173]

Basic syntax for a inner join in a update

update t1
set field1 = t2.someotherfield
from table1 t1
join table2 t2 on t1.someid = t2.someid
where t2.myfield = 'test'

This is code from thread183-1509886
Posted by SQLSister

 

[TornierIT]

Thanks for the quick reply. So this will look for ALL the records in the measurement table and find the correct field value based on the above criteria?

For Instance:

Surg_Type Table

Surgery_Num Surg_Type
00001
00002
00003



Measurement Table:

srgID msmID Surg_Type
00001 00001 Refraction
00001 00002 LRI
00001 00003 TORIC
00001 00004 Refraction
00002 00005 Refraction
00002 00006 Refraction
00002 00007 Final Refraction
00003 00008 Refraction
00003 00009 Refraction
00003 00010 LRI
00003 00011 Refraction
00003 00012 Refraction

The results should be:

Surg_Type Table

Surgery_Num Surg_Type
00001 LRI+TORIC
00002 Final Refraction
00003 LRI

 

[bborissov]

And what if:
[tt]
Surgery_Num Surg_Type
00001
00002
00003
00004


Measurement Table:

srgID msmID Surg_Type
00001 00001 Refraction
00001 00002 LRI
00001 00003 TORIC
00001 00004 Refraction
00002 00005 Refraction
00002 00006 Refraction
00002 00007 Final Refraction
00003 00008 Refraction
00003 00009 Refraction
00003 00010 LRI
00003 00011 Refraction
00003 00012 Refraction

00004 00013 Refraction

---The results should be: ??????

Surg_Type Table

Surgery_Num Surg_Type
00001 LRI+TORIC
00002 Final Refraction
00003 LRI
00004 ?????????????
[/tt]


Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[TornierIT]

It remains refraction. That's the lowest in the hierarchy that the field can be.

 

[bborissov]

And how many Surg_Types you have? How many combinations between them?
Now you have 4 types and 5 combinations (in the example you show us) but what will happens in the future?
If you need to add one more what will happens? How do you know what have lower or upper hierarchy?
Also Is this ONE time job or you should do this on regular bases?
If it is one time job then you can hard code it, but if not...

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[TornierIT]

This was going to be used in a stored procedure.

As far as any future additions, if I see the code and it works correctly, I can add any future ones.

I am just puzzled on how to make sure the correct Surgery Type gets put into the Surg_Type table.

Here is the Hierarchy from lowest to highest:

1) NULL
2) 'Final Refraction' or 'Refraction'
3) 'TORIC' or 'LRI'
4) 'LRI+TORIC' - this one is not in a table and will be hard coded if criteria is met from above.

I am sorry if I am making this more confusing, but I am just puzzled on something that conceptually seems basic

 

[TornierIT]

I got it from another site:

UPDATE @Surg_Type
SET Surg_Type = CASE
WHEN Measure.LRI = 1 AND Measure.TORIC = 1 THEN 'LRI+TORIC'
WHEN Measure.LRI = 1 THEN 'LRI'
WHEN Measure.TORIC = 1 THEN 'TORIC'
ELSE 'Refraction' END
FROM @Surg_Type Surg_Type
INNER JOIN (
SELECT msmSurgery_srgIDFk,
MAX(CASE WHEN msmMeasurementType_lutIDFk IN (35,39) THEN 1 ELSE 0 END) AS Refraction,
MAX(CASE WHEN msmMeasurementType_lutIDFk IN (36) THEN 1 ELSE 0 END) AS LRI,
MAX(CASE WHEN msmMeasurementType_lutIDFk IN (37) THEN 1 ELSE 0 END) AS TORIC
FROM ORANGE.dbo.tblMeasurement
WHERE msmMeasurementType_lutIDFk IN (35,36,37,39)
GROUP BY msmSurgery_srgIDFk
) AS Measure ON Measure.msmSurgery_srgIDFk = Surg_Type.Surgery_Num
WHERE Surg_Type IS NULL