CODEWITHSUNDEEP
sqlsql-serversql-update
Saikiran Gosikonda
Saikiran Gosikonda

Reputation: 1035

SQL query to update a column value using a select query in SQL Server

I have tables as below:

Language

language_id   | language_name 
--------------------
   1             English
   2             Hindi

Book

 book_id   | language_id  | book_name
---------------------------------
   1             1            Head First Java
   2             1            Head First C
   3             2            Head First Java
   4             2            Head First C

Student

 student_id   | language_id  |  book_id
--------------------------------------
   1             1                1
   2             2                3

Now, I have to update the book_id column of Student table, with id value of another book where the current book_name = Head First Java, new book_name = Head First C and the new book must be of same language.

Sample Result: 

student_id   | language_id  |  book_id
--------------------------------------
   1             1                2
   2             2                4

I am facing issues to write a T-SQL query to do this. Please help me on this.

Upvotes: 0

Views: 139

Answers (3)

Mohit Solanki
Mohit Solanki

Reputation: 261

You have to pass any condition like this. 

 update Student set book_id = 2 where book_id = 1

OR

 update Student set book_id = 2 where book_id = 1 and student_id = 1

Upvotes: 0

Shushil Bohara
Shushil Bohara

Reputation: 5656

Please try it, It should address your requirement

UPDATE s SET s.book_id = b.book_id
FROM [language] l
INNER JOIN (SELECT language_id, 
                MAX(book_id) book_id 
            FROM book 
            GROUP BY language_id ) b ON b.language_id = l.language_id 
INNER JOIN student s ON s.language_id = b.language_id

Upvotes: 0

Raphaël Althaus
Raphaël Althaus

Reputation: 60493

Think you need to use an alias for update, to find the right language in the setting subquery.

update s
set s.book_id = (select book_id from book 
                 where book_name = 'Head First C'
                and language_id = s.language_id)
from student s
where s.book_id in (select book_id from book where book_name = 'Head First Java')

Upvotes: 1

Related Questions