CODEWITHSUNDEEP
sqlsql-server
Graviton
Graviton

Reputation: 83326

Update data in different tables in SQL Server

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?

Upvotes: 1

Views: 1223

Answers (3)

Dheer
Dheer

Reputation: 4076

Syntax in Oracle would be:

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

Upvotes: 0

Vinko Vrsalovic
Vinko Vrsalovic

Reputation: 340526

Tested on SQL Server 2005

update table2 set pubVisited = NULL 
from 
    table1 t1 
        inner join 
    table2 t2 
    on (t1.playerID = t2.playerID and t1.nationalities = 'England')

Upvotes: 4

pilsetnieks
pilsetnieks

Reputation: 10430

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.

Upvotes: 3

Related Questions