Update statement - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Update statement

Author

Topic

byomjan
Starting Member

34 Posts

Posted - 2009-12-23 : 14:15:43

TABLE STUDENT
(
ROLL_ID INT,
NAME VARCHAR(10))

1, 'HARY'
2, 'JOHN'
3, 'TOM'
4, 'KING'

TABLE LIBRARY
(BOOK_ID INT
ROLL_ID INT,
EXCEPTION_ID CHAR(1))

101, 1, 1
102, 1, 1
103, 2, 1
104, 3, 1

TABLE EXCEPTION

(EXCEPTION_ID INT,
EXCEPTION_STATUS_CODE VARCHAR(4),
EXCEPTION_STATUS VARCHAR(10))

1, 5 , 'OPEN'
2, 10 , 'CLOSED'
3, 15, 'ERROR'

I am trying to UPDATE the exception_id of LIBRARY table where EXCEPTION_STATUS_CODE = 15 from EXCEPTION
for those BOOK_ID having issued to more than one ROLL_ID.

Can you please advise.

Byomjan....

byomjan
Starting Member

34 Posts

Posted - 2009-12-23 : 14:17:20

This SQL will not work , as i need to update EXCEPTION_ID for corresponding BOOK_ID .But i cant have BOOK_ID in the SELECT list .

I m confused.

UPDATE LIBRARY
SET EXCEPTION_ID =
(SELECT EXCEPTION_ID FROM EXCEPTION WHERE EXCEPTION_STATUS_CODE = 15)
FROM
(
select ROLL_ID ,count(BOOK_ID) as cnt
FROM
STUDENT as a
JOIN LIBRARY as b ON a.BOOK_ID =b.BOOK_ID
group by ROLL_ID
having count(BOOK_ID) >1
) as x
JOIN LIBRARY as Y ON x.BOOK_ID=y.BOOK_ID

Byomjan....

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-12-23 : 14:43:22

so what are you trying to do? if a student has more than one book checked out update exceptionid to 15 which is 'ERROR'?

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

byomjan
Starting Member

34 Posts

Posted - 2009-12-23 : 18:51:52

yes correct

Byomjan....

byomjan
Starting Member

34 Posts

Posted - 2009-12-28 : 13:13:06

any advice on this?

Byomjan....

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-12-28 : 13:30:59

Your description sounds like the error situation is if a single book is checked out by more than one student (roll_id). And your code seems to support that. However, you say that byomjan is correct that the error is when a student has more than one book checked out - and your sample data seems to support that. What should be the result of your query based on your sample data? Which row(s) should be updated with 15?

Be One with the Optimizer
TG

byomjan
Starting Member

34 Posts

Posted - 2009-12-28 : 14:09:51

if you see the query ..

UPDATE LIBRARY
SET EXCEPTION_ID =
(SELECT EXCEPTION_ID FROM EXCEPTION WHERE EXCEPTION_STATUS_CODE = 15)
FROM
(
select ROLL_ID ,count(BOOK_ID) as cnt
FROM
STUDENT as a
JOIN LIBRARY as b ON a.BOOK_ID =b.BOOK_ID
group by ROLL_ID
having count(BOOK_ID) >1
) as x
JOIN LIBRARY as Y ON x.BOOK_ID=y.BOOK_ID

There is no BOOK_ID in the inner query X . It doesnt work .
The problem is I want to get the corresponding BOOK_ID which is issued more than 1 time and update its status.

Byomjan....

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-12-28 : 15:24:28

Let me try again...
In your sample data Roll_ID=1 (HARY) has the following Book_ids (101, 102) so your inner query corresponds to these 2 rows in Table_Library:


book_id       roll_id       exception_id
------------  -----------   ------------
101           1             1
102           1             1

Which of these rows (book_ids) do you want to update to an error exception ?

Be One with the Optimizer
TG

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-12-28 : 17:21:01

If the answer to my last question is "both rows" then this code would work:


update l set
       l.exception_id = e.exception_id
from   (
       select roll_id
       from   library
       group by roll_id
       having count(book_id) > 1
       ) x
join   library l
       on l.roll_id = x.roll_id
join   exception e
       on e.EXCEPTION_STATUS_CODE = 15

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources