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.
| Author |
Topic |
|
huddie
Starting Member
6 Posts |
 Posted - 2006-09-15 : 10:24:31
|
I have two tables in an inner join. I'm detailing the tables with some of their fields below. These tables are in a database I'm creating to manage backup tapes. Most importantly, this database will inform me when backup tapes which have already been used can be recycled (e.g. after all the jobs on the tape are over 28 days old). I want to write something which will look at each tape in turn and, if all related backup jobs on that tape are aged, the tape status will be changed from Active to Spare.Tapes--TapeNo--Status (Spare / Assigned)Jobs--JobNo--Name--Description--TapeNo--AgedJob (BIT field indicating whether or not the job has aged)Each tape can have 0, 1 or many jobs and each job can be on more than one tape.Anyway, I have the tables and relationsips set up and they're ok. Again, what I'm struggling with is how I take each tape and look at all its jobs and, if all have aged, change the Status for the tape to Spare. I'm using SQL Server 2000 (Access 2003 as front end) and am pretty new to SQL. I was thinking this could be done by using some kind of update query and subquery, but I'm stumped. Could someone please help ? --Paul Anderson |
|
|
LazyDragon
Starting Member
30 Posts |
Posted - 2006-09-15 : 10:36:52
|
| Here you go.UPDATE TapesSET Status = 'Spare'WHERE Status = 'Active'AND NOT EXISTS(SELECT 1 FROM Jobs J WHERE J.TapeNo = Tapes.TapeNo AND J.AgedJob = 0)LazyDragon |
 |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-09-15 : 10:44:03
|
| [code]update Tapesset Status = 'Spare'where Status <> 'Spare' and TapeNo not in ( Select TapeNo from Jobs where AgedJob <> 1 )[/code]CODO ERGO SUM |
|
|
|
huddie
Starting Member
6 Posts |
Posted - 2006-09-19 : 11:23:51
|
| Thanks guys. Michael's solution did the trick. I tried it on Saturday. I'm getting the hang of basic SQL and techniques like this are useful.--Paul Anderson |
|
|
|
|
|
|
Use Update Query in SQL Server 2000 ?