Update data in different tables in SQL Server

Question

In a table I have the following schema

table1:
playerID int primary key,
nationalities nvarchar

table2:
playerID int,
pubVisited nvarchar

Now, I want to set all the players' playedVisited to null, for player whose nationality is "England", any idea on how to do this?

Answer 1

Judging by the nvarchar type, you're using MSSQL. Now, in MySQL you could use a subselect in the update .. where clause but MSSQL has its own update .. from clause:

UPDATE table2
SET
    table2.pubVisited = null
FROM table1
WHERE
    table2.playerID = table1.playerID and table1.nationalities = 'England'

Haven't tested it though, so it might not work.

Answer 2

Syntax in Oracle would be update table2 set playedvisited = NULL where playerID in (select playerID from table1 where nationalities = 'England')

Answer 3

Tested on SQL Server 2005

update table2 set pubVisited = NULL from table1 t1, table2 t2 where 
t1.playerID = t2.playerID and t1.nationalities = 'England'