Basically, what I want to accomplish are to UPDATE the DEPARTMENT_NAME from TV_LABCASE table and to UPDATE the CHARGE from TV_LABCASE table based on CASE_KEY. But I do not know how to update a table that was created using an INNER JOIN

TV_LABCASE table:

This TV_LABCASE table was queried using this:

SELECT TOP 10 
    C.CASE_KEY, C.DEPARTMENT_CASE_NUMBER, 
    D.DEPARTMENT_NAME, O.OFFENSE_DESCRIPTION AS CHARGE, 
    LAB_CASE, OFFENSE_DATE
FROM
    TV_LABCASE C
INNER JOIN 
    TV_DEPTNAME D ON C.DEPARTMENT_CODE = D.DEPARTMENT_CODE
INNER JOIN 
    TV_OFFENSE O ON C.OFFENSE_CODE = O.OFFENSE_CODE
ORDER BY 
    CASE_DATE DESC 

TV_DEPTNAME table:

This TV_DEPTNAME table was queried using this:

SELECT * 
FROM TV_DEPTNAME

TV_OFFENSE table:

This TV_OFFENSE table was queried using this:

SELECT * 
FROM TV_OFFENSE

This is the result in the TV_LABCASE table after the update:

CodePudding user response：

You're misunderstanding how relational databases work - the table was not created using an INNER JOIN, you wrote a query that is using an INNER JOIN and the values you said you want to change are coming from the joined tables. What you want to change with your UPDATE is the values in the TV_LABCASE table DEPARTMENT_CODE and OFFENSE_CODE columns.

When you change their values then they will change which rows are joined by query in future, so for example you could write an update like this

UPDATE TV_LABCASE 
SET DEPARTMENT_CODE = 100, OFFENSE_CODE = 'ABT' 
WHERE CASE_KEY = 4235

That will result in the values shown in your query result changing from OHIO to AMERICA and from STEALING to ABORTION.