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

Question

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.

So you want to know how to write an UPDATE-Statement in SQL-Server? — Nebi
– Nebi, Commented Sep 12, 2016 at 9:39
yes, I have mentioned the conditions in the question. Current book name should be Head First Java, new one should be Head First C and the language of both books should be same. — Saikiran Gosikonda
– Saikiran Gosikonda, Commented Sep 12, 2016 at 9:40

Raphaël Althaus · Accepted Answer · 2016-09-12 09:48:02Z

1

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')

answered Sep 12, 2016 at 9:48

Raphaël Althaus

60.6k6 gold badges106 silver badges127 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

AT-2017 Over a year ago

I guess, you need to update the language id as well.

Raphaël Althaus Over a year ago

@AT-2016 no. You have to keep the language.

Shushil Bohara · Accepted Answer · 2016-09-12 10:00:19Z

0

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

answered Sep 12, 2016 at 10:00

Shushil Bohara

5,6662 gold badges19 silver badges32 bronze badges

Comments

Jibin Balachandran · Accepted Answer · 2016-09-12 12:54:49Z

0

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

edited Sep 12, 2016 at 12:54

Jibin Balachandran

3,4491 gold badge27 silver badges39 bronze badges

answered Sep 12, 2016 at 9:56

Mohit Solanki

2613 silver badges17 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related