CODEWITHSUNDEEP
sqlsql-server
Mannya
Mannya

Reputation: 135

How to copy column from one table into another table in SQL Server

I want to copy column state_name from table state into table district. here is my query.

This code is working on mysql but not in SQL Server

UPDATE district,state
SET    district.state_name = state.state_name
WHERE  district.state_id = state.id

enter image description here

This is the state table

enter image description here

This is the district table

Upvotes: 0

Views: 54

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271031

In SQL Server, the corresponding syntax might be:

UPDATE district
     SET state_name = s.state_name
    FROM state s
    WHERE district.state_id = s.id;

This is more commonly written using an explicit JOIN:

UPDATE d
     SET state_name = s.state_name
    FROM district d JOIN
         state s
         ON d.state_id = s.id;

However, you probably shouldn't be updating the value at all. Just use a JOIN to get the state name when you need it.

Upvotes: 1

Thom A
Thom A

Reputation: 96013

In T-SQL, an UPDATE can only be followed by a single object afterwards. I suspect what you want woulkd be:

UPDATE d
SET state_name = s.state_name
FROM dbo.district d
     JOIN dbo.state ON d.state_id = s.id;

Upvotes: 0

Related Questions