Help with Update query

[Almie]

I am trying to update Table1 AVTGUID column with the AVTGUID data from Table2. When I run my update query, both avtguid columns in Table 1 are updated with the avtguid from the first record in Table 2.

Table 1 - getting avtguid updated
avtguid offense_id offense_typ_id effective_date end_date
NULL 10-1083F 100013 1900-01-04 1995-12-31
NULL 10-1083F 100024 1900-01-04 1995-12-31

Table 2 - Has avtguid information
avtguid offense_id offense_typ_id effective_date end_date
0AE7B707-87E7-41BF-B950-BE205EF0F2F2 10-1083F 100013 1900-01-04 1995-12-31
2669184D-7D48-4D51-973C-C4A6582F26F8 10-1083F 100024 1900-01-04 1995-12-31

Table 1 - AFTER UPDATE QUERY IS RUN
avtguid offense_id offense_typ_id effective_date end_date
0AE7B707-87E7-41BF-B950-BE205EF0F2F2 10-1083F 100013 1900-01-04 1995-12-31
0AE7B707-87E7-41BF-B950-BE205EF0F2F2 10-1083F 100024 1900-01-04 1995-12-31

This is what my query looks like
UPDATE ajacs_avt..ACM_STATUTE
SET AVTGUID = B.AVTGUID
FROM ajacs_avt..ACM_STATUTE A
JOIN ajacs_avt_gj_master..ACM_STATUTE B
on A.OFFENSE_REF_NUM = B.OFFENSE_REF_NUM
JOIN TABLE_IMPORTS..pinal_OFFENSE_TYP YY
ON A.OFFENSE_TYP_ID = YY.db_OFFENSE_TYP_ID
WHERE A.OFFENSE_REF_NUM = B.OFFENSE_REF_NUM
AND A.OFFENSE_TYP_ID = YY.DB_OFFENSE_TYP_ID

I am joining the offense_typ_id from a different table.

What I also noticed that when I select the data, I get 4 records instead of 2.....
avtguid avtguid
NULL 0AE7B707-87E7-41BF-B950-BE205EF0F2F2
NULL 0AE7B707-87E7-41BF-B950-BE205EF0F2F2
NULL 2669184D-7D48-4D51-973C-C4A6582F26F8
NULL 2669184D-7D48-4D51-973C-C4A6582F26F8


-- select a.avtguid,b.avtguid
FROM ajacs_avt..ACM_STATUTE A
JOIN ajacs_avt_gj_master..ACM_STATUTE B
on A.OFFENSE_REF_NUM = B.OFFENSE_REF_NUM
JOIN TABLE_IMPORTS..AVT_OFFENSE_TYP YY
ON A.OFFENSE_TYP_ID = YY.db_OFFENSE_TYP_ID
WHERE A.OFFENSE_REF_NUM = B.OFFENSE_REF_NUM
AND A.OFFENSE_TYP_ID = YY.DB_OFFENSE_TYP_ID
and a.offense_ref_num = '10-1083F'

What is wrong with the update query?

 

[Almie]

I figured out my problem......distinct! DUH!

 

[TamarGranor]

Seems to me you also are repeating the join information in the WHERE clause, which you don't need to do. That is, you don't need this in the WHERE, since you already have it in the JOIN:

A.OFFENSE_REF_NUM = B.OFFENSE_REF_NUM
AND A.OFFENSE_TYP_ID = YY.DB_OFFENSE_TYP_ID

Tamar