Updating this flg... How? - 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 2000 Forums

Transact-SQL (2000)

Updating this flg... How?

Author

Topic

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2004-10-26 : 04:50:03

I have this following data...

ID - EndingAmount - flgPaid
-------------------------------------------------------
14344 - 500.00 - 0
14344 - 400.00 - 1
14344 - 300.00 - 1
14344 - 200.00 - 1
14344 - 100.00 - 1
14344 - 000.00 - 1

I want to set the record of iD 14344's flgPaid to 1 if EndingAmount = 0.

actually i have my querry...

---------------------------My Querry---------------------------------------------------
DECLARE @AmountIsZero as int

SET @AmountIsZero = (select count(*) from tblAida where EmpID=14344 and endingamount= 0 AND flgPaid=0)
IF @AmountIsZero > 0
BEGIN
UPDATE tblAida SET flgPaid = 1 WHERE EmpID = 14344 AND flgPaid=0
END
---------------------------------------------------------------------------------------

But im looking for the way to minimize my querry by using i guess
case or if? The short line the better right?

Please how?

Want Philippines to become 1st World COuntry? Go for World War 3...

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-26 : 05:09:35

so this is not ok?

UPDATE tblAida SET flgPaid = 1 WHERE EmpID = 2 AND flgPaid=0 AND endingamount=0

Go with the flow & have fun! Else fight the flow

nr
SQLTeam MVY

12543 Posts

Posted - 2004-10-26 : 05:11:46

update tbl
set flgPaid = 1
from tbl t1
where flgPaid = 0
and exists (select * from tbl t2 where t2.id = t1.id and t2.EndingAmount = 0)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2004-10-26 : 05:14:17

Thnx for your reply spirit1 but your querry will only update 1 record...
What happen to records where endingamount not equal to zero?
they will not be updated too...

I want to update id 14344's flgPaid to 1 if endingamount = 0 and flgPaid = 0

Tnx for the response.

Want Philippines to become 1st World COuntry? Go for World War 3...

nr
SQLTeam MVY

12543 Posts

Posted - 2004-10-26 : 05:36:47

if you don't like that how about

update tbl
set flgPaid = 1
from tbl t1
join (select distinct id from tbl where EndingAmount = 0) t2
on t2.id = t1.id
where t1.flgPaid = 0

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2004-10-26 : 20:02:16

Thanks for your reply nr!

Both of your suggestion are working but i prefer to use your first querry...

thanks...

Want Philippines to become 1st World COuntry? Go for World War 3...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources