Refreshing data in different tables in SQL Server
In the table, I have the following schema
table1:
playerID int primary key,
nationalities nvarchar
table2:
playerID int,
pubVisited nvarchar
Now, I want all lost players to be ignored down to zero, for a player whose nationality is England, any idea on how to do this?
+1
Graviton
source
to share
3 answers
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')
+4
Vinko Vrsalovic
source
to share
From the nvarchar type you are using MSSQL. Now in MySQL you can use a subquery in the update .. where the clause, but MSSQL has its own update .. from the clause:
UPDATE table2
SET
table2.pubVisited = null
FROM table1
WHERE
table2.playerID = table1.playerID and table1.nationalities = 'England'
Haven't tested it, although it might not work.
+3
pilsetnieks
source
to share
Syntax in Oracle:
update table2
set playedvisited = NULL
where playerID in (select playerID
from table1
where nationalities = 'England')
0
Dheer
source
to share