update status? - 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

General SQL Server Forums

New to SQL Server Programming

update status?

Author

Topic

gongxia649
So Suave

344 Posts

Posted - 2006-05-12 : 11:30:34

table1 has 3 fields:
bookNumber
123
234
345

status -- status defaul value is 0

partnerNumber
1
2
3

table 2 has 2 fields
bookNumber
234
345
567
789
partnerNumber
1
2
3
4

A stored procedure named remove_books, which remove books if the conditions are true.

If stored procedure is executed. That means, it removes the bookNumbers from table2 if the conditions are true. Lets say it removes 234 from table2

then
do update in table1 status = 1 for BookNumber 234.

gongxia649
So Suave

344 Posts

Posted - 2006-05-12 : 13:17:40

is my q not clear?

Kristen
Test

22859 Posts

Posted - 2006-05-12 : 13:19:18

No, I'm afraid it wasn't to me.

gongxia649
So Suave

344 Posts

Posted - 2006-05-12 : 15:03:09

table1 has 2 fields:
bookNumber -- like 123, 234, 345, ...ect
status -- status defaul value is 0

A stored procedure named remove_books, which remove books if the conditions are true.

If stored procedure is executed. That means, it removes the bookNumbers if the conditions are true. Lets say it removes 123, 234.

then
do update status = 1 for 123 and 234.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-05-12 : 15:03:31

So do you want the update to occur outside of the stored procedure or inside of it? If outside, then you will need to know which books were removed. In order to do that, then you'd have to record this somewhere prior to the removal. If inside, then you just run the update prior to the removal using the same criteria.

Tara Kizer
aka tduggan

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-05-12 : 15:06:19

Looking at your latest post, I'm now even more confused. If you delete the books from the table, then there is no corresponding row to update it to 1. Thus my confusion. Providing more sample data might help. Show us what the data should look like prior to the stored procedure running, after the stored procedure has run, and after the update statement. So you should provide 3 sets of sample data that fully illustrate your problem.

Tara Kizer
aka tduggan

gongxia649
So Suave

344 Posts

Posted - 2006-05-18 : 13:56:04

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-05-18 : 14:00:38

Do you have a question?

CODO ERGO SUM

gongxia649
So Suave

344 Posts

Posted - 2006-05-18 : 14:19:54

oh! i edited the post with the question.

i like to update status = 1 in table 1.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-05-18 : 14:28:07

So the stored procedure must know that it is deleting 234. So add an extra line in it:

update table1 set status = 1 where BookNumber = @BookNumber
or whatever your variable is that knows what book it is deleting.

Tara Kizer
aka tduggan

svicky9
Posting Yak Master

232 Posts

Posted - 2006-05-18 : 14:46:15

Do you want to change the status to 1 in table 1 when the row in table 2 is deleted???

Vic

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-05-18 : 15:06:47

That's how I understand it.

DECLARE @BookNumber int

SET @BookNumber = 234

DELETE FROM Table2 WHERE BookNumber = @BookNumber

UPDATE Table1 SET Status = 1 WHERE BookNumber = @BookNumber

Tara Kizer
aka tduggan

svicky9
Posting Yak Master

232 Posts

Posted - 2006-05-18 : 15:14:58

Try this trigger if you want your solution

create trigger trg_remove_books on table2
for delete
as update table1 set status =1 from table1 join table2 on table1.booknumber <>table2.bookno

Vic

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-05-18 : 15:26:13

quote:
Originally posted by svicky9

Try this trigger if you want your solution

create trigger trg_remove_books on table2
for delete
as update table1 set status =1 from table1 join table2 on table1.booknumber <>table2.bookno

Vic

Vic, your trigger will update all rows in the table1 table and not just the ones impacted by the delete. You need to reference the deleted trigger table instead of table2.

gongxia649, do not use this trigger as is. It will update all of your rows, which is not what you want.

Tara Kizer
aka tduggan

svicky9
Posting Yak Master

232 Posts

Posted - 2006-05-18 : 15:33:25

oops a mistake...how do you reference the deleted table there

Vic

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-05-18 : 15:35:23

create trigger trg_remove_books on table2
for delete
as
update t
set status = 1
from table1 t
inner join deleted d
on t.booknumber <> d.booknumber

Tara Kizer
aka tduggan

gongxia649
So Suave

344 Posts

Posted - 2006-05-18 : 15:42:36

thanks but i think i solved the problem a few hours ago.

svicky9
Posting Yak Master

232 Posts

Posted - 2006-05-18 : 15:43:22

a mistake there..it should be <b>t.booknumber = d.booknumber<b>

Thanks tara..

Vic

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-05-18 : 15:45:42

quote:
Originally posted by svicky9

a mistake there..it should be <b>t.booknumber = d.booknumber<b>

Thanks tara..

Vic

That wasn't the only problem. Notice in my version deleted replaces table2 in the update query. You'll need to read about the trigger tables in SQL Server Books Online for more details. The tables are called inserted and deleted.

Tara Kizer
aka tduggan

cmdr_skywalker
Posting Yak Master

159 Posts

Posted - 2006-05-18 : 20:11:10

I think the initial confusion was "when the condition is true". It is probably better to elaborate phrases such as "when the condition is true" on a technical term (what field and value is considered true). Anyway, we hope we can be more helpful to you next time :). That is, when the condition is true :).

May the Almighty God bless us all!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources