Help with 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 2000 Forums

Transact-SQL (2000)

Help with UPDATE statement

Author

Topic

jamonakes
Starting Member

18 Posts

Posted - 2006-04-06 : 10:02:30

I have a table, tblEntrants, with the following fields: rafID (PK), rafCellNo, rafDateCommitted, rafPromoID, rafPrizeWon, rafBolHasWon. This table has 5000 rows, and 280 of the rows have rafBolHaswon = 1, the rest have rafBolHaswon = 0. Amongst the 4720 rows with rafBolHasWon = 0, there are some rows that are similar in every way to some of the rows which have rafBolHasWon = 1, the only other difference besides the rafBolHasWon column value, is the rafID, which is a unique ID for each row.

I have another table, tblEntrants_Clean, which is has a similar schema to tblEntrants and also has the 5000 rows, though not arranged in the same order as those of tblEntrants (rafID is not in sync for the 2 tables e.g. the 1000th row for tblEntrants could be the 1500th row for tblEntrants_Clean). However, no row in tblEntrants_Clean has rafBolHasWon = 1. I want to update the 280 rows in the tblEntrants_Clean, setting the rafBolHaswon = 1. I can only accurately do so by reading rafCellNo from tblEntrants where rafBolHasWon = 1, and updating tblEntrants_Clean’s rafBolHasWon to 1 WHERE the rafCellNo = to the one from tblEntrants. The problem is if I do that, the statement will update ALL rows in tblEntrants_Clean where rafCellNo = to the one from tblEntrants, yet all I want is the statement to update ONLY the first match where rafCellNo = to the one from tblEntrants. How can I go about this?

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2006-04-06 : 21:34:58

Let's see if I understand you correctly. To simplify your problem, if you had data like this:


tblEntrants:
rafid       rafcellno   rafBolHasWon 
----------- ----------- ------------ 
1           100         1
2           100         0
3           200         1
4           200         1
5           300         0

tblEntrants_clean:
rafid       rafcellno   rafBolHasWon 
----------- ----------- ------------ 
1           200         0
2           100         0
3           300         0
4           200         0
5           100         0

Then you want your results to be like this?


rafid       rafcellno   rafBolHasWon 
----------- ----------- ------------ 
1           200         1
2           100         1
3           300         0
4           200         1
5           100         0

Is it correct to assume that rafCellno is not unique and that rafid is the only unique key?

jamonakes
Starting Member

18 Posts

Posted - 2006-04-11 : 10:28:57

yes, it is correct to assume that the only unique ID is rafID and that rafCellNo is not unique. Also, the structure u have done correctly depicts what I want achieved.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources