T-SQL query with funny behavior

Question

Let's say you have the following two tables:

X Table

X_ID Y_ID_F X_Value

1     1     Q
2     1     G
3     1     T
4     2     W
5     2     K
...

Y Table

Y_ID Y_Value

1     A
2     B
...

You want to query only those properties whose Y parent's value is A and update them so you write a query as follows (I realize there is a better query but bear with me):

UPDATE X set X_Value = 'O' 
WHERE X_ID IN
(
select distinct X.X_ID FROM X
INNER JOIN Y ON X.Y_ID_F = Y.Y_ID
WHERE Y.Y_Value = 'A'
)

I previously thought that this would do what it seemingly says : Update the rows of the X table where the joined Y table's Y_Value = 'A'. However it seems that the X.X_ID causes all rows in the X table to be updated, not just the ones you'd think the WHERE clause restricted it to. Somehow that X.X_ID causes the query to completely ignore the attempt of the where clause at restricting the set of updated rows. Any ideas on why?

EDIT: I think it may have something to do with the way the tables in the database I am querying are associated and not as simple as the example I tried to equivocate it to.

Answer 1

I've never had luck with sub-selects in an UPDATE statement. Instead, try something like the following:

UPDATE X SET X_Value = 'O'
FROM Y
WHERE Y.Y_ID = X.Y_ID_F
  AND Y.Y_Value = 'A'

Answer 2

This didn't make any sense to me either, so I tried it. After running the update statement, the contents of table X looks like this:

X_ID   Y_ID   X_VALUE
1      1      O
2      1      O
3      1      O
4      2      W
5      2      K

In other words, the results I expected. Are you sure there isn't something else at work here? I did notice a minor anomaly in your example query: A reference to 'X.Y_ID_F' in the join condition where I would expect to see just 'X.Y_ID'. I assumed it was just a typo.