Help with a join query

[nondrinker]

Hello,
I am trying to update the TITLE_ID field in the AR3 table,
where the AR3.NAME = AR4.NAME
AND AR3.CITY = AR4.CITY.
Here is my query:

**************************************
UPDATE AR3
SET TITLE_ID = AR4.TITLEID
FROM AR3 INNER JOIN AR4 ON AR3.NAME = AR4.NAME
WHERE AR3.CITY = AR4.CITY
***************************************
This query is updating the TITLE_ID field in the AR3 table where the NAME values are same, even though the City values are different.

I am trying to update the TITLE_ID field only where the NAME values are same AND and CITY values are same also, not just one of them

Thank you.

 

[mercwrought]

That is odd that should only update where the names are the same and the citys are the same.

Here is another way of writing it

UPDATE AR3
SET TITLE_ID = AR4.TITLE_ID
FROM AR3 INNER JOIN AR4 ON AR3.NAME = AR4.NAME and AR3.CITY = AR4.CITY

to check it before update run

select AR3.NAME,AR3.CITY,AR4.NAME,AR4.CITY from 
FROM AR3 INNER JOIN AR4 ON AR3.NAME = AR4.NAME and AR3.CITY = AR4.CITY

 

[nondrinker]

I have tried using AND instead of WHERE but it does the same thing, i.e. it updates where the names are same even if city is different.

 

[ESquared]

What do you get with the select statement?

Do you have a trigger running on that table?

-------------------------------------
Only the unvirtuous can be dutiful, for the virtuous already perform duty's requirements by inclination and for pleasure. Where there is no pain, no disinclination, there is no duty.
- Erik E

 

[nondrinker]

I did get the 4 columns with 2 identical pairs of values, but somehow the update doesn't work.
No i don't have any trigger running on this table.

 

[mercwrought]

that is realy odd i have to agree with ESquared and ask if there is a trigger on the table, also could you copy and paste your exact code here.

 

[nondrinker]

Here it is:

******************************
UPDATE AR33
SET TITLE_ID = TITLECOMPANY.TITLEID2
FROM AR33 INNER JOIN TITLECOMPANY ON AR3.TITLE_CO_NAME = TITLECOMPANY.NAME
AND AR3.TITLE_CO_CITY_ST = TITLECOMPANY.CITYSTATEZIP

******************************
One thing i just noticed. I have added a new counter field named TITLEID2 in the TitleCompany table and now i was trying to update the values from that field, but somehow its still showing me the same old values, though every time i run the query it shows me that 5445 rows affected.

I tried running an update query on ar3 table only with the
update ar3 set title_id = (any number) where title_co_name = (the actual name) and it worked fine.

 

[nondrinker]

A correction, in my query,
whereever i've written AR33, thats actually AR3.
So the actual syntax is:

UPDATE AR3
SET TITLE_ID = TITLECOMPANY.TITLEID2
FROM AR3 INNER JOIN TITLECOMPANY ON AR3.TITLE_CO_NAME = TITLECOMPANY.NAME
AND AR3.TITLE_CO_CITY_ST = TITLECOMPANY.CITYSTATEZIP

 

[ESquared]

All I can say is, weird.

 

[mercwrought]

ok that is werid. what server / service pack you running?
is either a view?