How to use multiple columns in WHERE clause in SQL Server in update statement

Question

I am trying to write a SQL/XML query in SQL Server to update a certain row in the database table. I have two tables: the first called edition with columns id, year, translations, book.

Example

• id = "17"
• year = "1997"

• translations =

  <Translations>
       <Translation Language="English" Price="120" />
       <Translation Language="Russian" Price="110" />
  </Translations>
  

• book = "11"

The second table called book having the columns: id, title. Example(id = "11" , Title = "Encore une fois")

I have written the following query which so far works fine

UPDATE edition
SET translations.modify('insert element Translation {attribute Language 
{"Norwegian"}, attribute Publisher {"KLC"}, attribute Price {200}} as last 
into (/Translations)[1]');

But now I want to add conditions to my statement using WHERE clause like the following but I keep getting an error

An expression of non-boolean type specified in a context where a condition is expected

WHERE(book , year) IN (SELECT id, MAX(year) 
                       FROM book AS b, edition AS e
                       WHERE title = 'Encore une  fois'
                         AND b.id = e.book
                       GROUP BY 1)

The reason for adding to the condition is to update the latest edition only. how can i modify the condition to be accepted in SQL Server?

Answer 1

Try:

UPDATE edition
SET ...
FROM edition INNER JOIN book ON edition.book = book.id
WHERE title = 'Encore une fois'
AND year IN (SELECT MAX(e2.year)
             FROM edition AS e2
             WHERE e2.book = book.id)