Update query - 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)

Update query

Author

Topic

1sqlover
Yak Posting Veteran

56 Posts

Posted - 2006-05-26 : 16:32:21

SELECT * FROM ARCHIVELOG

|Volume |StorageDrive |Folder |FolderCreated
2090 D:\PHOTOS 289 1
-----------------------------------------------
2090 E:\ARCHIVE 290 1
-----------------------------------------------
2091 D:\PHOTOS 289 1
-----------------------------------------------
2091 NO ARCHIVE 290 0
-----------------------------------------------
2092 D:\PHOTOS 291 1
-----------------------------------------------
2092 NO ARCHIVE 291 0
-----------------------------------------------
2093 D:\PHOTOS 292 1
-----------------------------------------------
2093 NO ARCHIVE 292 0
-----------------------------------------------

These are the results I get when I do "SELECT * FROM ARCHIVELOG", how can I create a view that will select all of the rows that have a 0 (as in the archive has not been created) but will display the "StorageDrive" of the previous record?

In other words I want my results to look like this

|Volume |StorageDrive |Folder |Archived
2091 D:\PHOTOS 289 0
-----------------------------------------------
2092 D:\PHOTOS 291 0
-----------------------------------------------
2093 D:\PHOTOS 292 0
-----------------------------------------------

Is this achievable? If yes, how can I query this and insert it into another DB?

epoh

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-26 : 20:23:15

"Is this achievable?"
Yes

select Volumne, 
       (select top 1 StorageDrive from ARCHIVELOG x where x.Volumn < a.Volumn order by Volumn desc),
       Folder, 
       0 as Archived
from   ARCHIVELOG a
where  a.FolderCreated = 0

"how can I query this and insert it into another DB?"

insert into db2.dbo.Table(...)
select ...
from   ...
where  ...

1sqlover
Yak Posting Veteran

56 Posts

Posted - 2006-05-26 : 21:05:43

Thanks,

How can I get the Top 1 to be the name of a column? Right now all I get is: No column name

epoh

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-05-26 : 21:18:55

[code]select Volumne,
(select top 1 StorageDrive from ARCHIVELOG x where x.Volumn < a.Volumn order by Volumn desc) as [Storage Drive] ,
Folder,
0 as Archived
from ARCHIVELOG a
where a.FolderCreated = 0[/code]

Srinika

1sqlover
Yak Posting Veteran

56 Posts

Posted - 2006-05-26 : 21:25:39

Sorry never mind I figured that one out.

epoh

1sqlover
Yak Posting Veteran

56 Posts

Posted - 2006-05-26 : 21:26:24

thanks

epoh

1sqlover
Yak Posting Veteran

56 Posts

Posted - 2006-05-26 : 21:47:00

I guess I should have specified earlier. Volume and Folder are subfolders to StorageDrive.

The purpose of StorageDrive is to record whether or not the items have been archived or not.

For the purposes of not knowing the correct term I will call the two records with the same information in "Volume" and "Folder" a "Group"

The "first" record in the "Group" indicates the following: StorageDrive (is the drive letter and basic path of the where the files are located). *Volume is a subfolder to StorageDrive and Folder is a subfolder to Volume. Files are in the "Folder" field

The "second" record in the "Group" indicates the following: StorageDrive (is the drive letter and basic path of the Archive). *Same as above

I played with the sql stmt that 'khtan' was kind enough to provide and it helped but I could not get it to work.

epoh

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-26 : 22:05:17

quote:
Volume |StorageDrive |Folder |Archived
2091 D:\PHOTOS 289 0

Can you explain how to obtain the above record ?

Or is this what you want ?

select Volume, 
       (select top 1 StorageDrive from ARCHIVELOG x where x.Volume = a.Volume and x.FolderCreated = 1 order by x.Folder) as [Storage Drive],
       Folder, 
       0 as Archived
from   ARCHIVELOG a
where  a.FolderCreated = 0

1sqlover
Yak Posting Veteran

56 Posts

Posted - 2006-05-26 : 22:17:23

Okay, this is what I have come up with, with your help:

SELECT STORAGEDRIVE, VOLUME,
(SELECT TOP 1 STORAGEDRIVE FROM ARCHIVELOG X
WHERE X.VOLUME = A.VOLUME) AS STORAGE,
FOLDER,
0 AS ARCHIVED
FROM ARCHIVELOG A
WHERE A.FOLDERCREATED = 0
ORDER BY A.VOLUME, A.FOLDER

And it returns the following:

STORAGEDRIVE,VOLUME,STORAGE,FOLDER,FOLDERCREATED,ARCHIVE
NO ARCHIVE ,1101,D:\PHOTOS,215,0
NO ARCHIVE ,1101,D:\PHOTOS,216,0
NO ARCHIVE ,1103,D:\PHOTOS,9,0
NO ARCHIVE ,1103,D:\PHOTOS,10,0
NO ARCHIVE ,1103,D:\PHOTOS,11,0
D:\PHOTOS,1104,D:\PHOTOS,1,0

Do you notice how StorageDrive and Storage are not equal. If there was a way to return those records that are not equal my query would be solved.

Thanks

epoh

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-26 : 22:21:01

So you have got what you wanted ?

1sqlover
Yak Posting Veteran

56 Posts

Posted - 2006-05-26 : 22:24:31

Close, but not quite. Is there a way to filter out the records that STORAGEDRIVE and STORAGE are not equal, from within this query.

epoh

1sqlover
Yak Posting Veteran

56 Posts

Posted - 2006-05-26 : 22:26:23

I am new to sql (as if that wasnt apparent, lol) I tried to do the following:

SELECT STORAGEDRIVE, VOLUME,
(SELECT TOP 1 STORAGEDRIVE FROM ARCHIVELOG X
WHERE X.VOLUME = A.VOLUME) AS STORAGE,
FOLDER,
0 AS ARCHIVED
FROM ARCHIVELOG A
WHERE A.FOLDERCREATED = 0
AND STORAGEDRIVE <> STORAGE
ORDER BY A.VOLUME, A.FOLDER

That didnt work because it was an alias, am I stuck?

epoh

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-26 : 22:27:58

Or this ?

select	a.Volume, b.StorageDrive, b.Folder
from	ARCHIVELOG a inner join
	(
		select	Volume, StorageDrive, Folder
		from	ARCHIVELOG
		where	FolderCreated	= 1
	) b
	on	a.Volume	= b.Volume
where	a.FolderCreated	= 0

"will display the "StorageDrive" of the previous record?"
Also i would like to highlight that records are not stored in database in any sequence or ordered. So there isn't a first record or last record, previous or next record. You define the sequence of the order retrieve using the ORDER BY.

So the statement "SELECT TOP 1 col FROM table" is not meaningful. You should always use TOP with ORDER BY.

1sqlover
Yak Posting Veteran

56 Posts

Posted - 2006-05-26 : 22:29:26

Thank you for pointing that out (the Order by). I will try this and see how it works.

epoh

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources