SQLTeam.com | Weblogs | Forums

Help with ms-sql2008 query

sql2008

#1

Hello, I would like to update a table with the following query. However, the update command never executes.

SELECT [Volume ID]
,[Attributes]
,[Path]
,[Pages]
,[VolumeName]
,[Date]
,[Deleted]
,[Maximum Size]
,[LowNotif]
,[Current Size]
,[LastUniqueID]
,[ID]
,[ServerName]
FROM [MacroImageDB].[dbo].[Volumes]
WHERE Attributes = 72 OR Attributes = 65536 OR Attributes = 104

update dbo.Volumes
set path = REPLACE ('\suffelstorage01.suffel-gmbh.de', '\suffelstorage01.suffel-gmbh.de', '\sufstor02.suffel-gmbh.de\CD') + VolumeName;


#2

That's the same as

set path = '\sufstor02.suffel-gmbh.de\CD\' + VolumeName;

and it will set the [path] by prefixing the VolumeName on EVERY row in the [Volumes] table - just checking that that is what you want? or were you wanting to ONLY update the rows:

??


#3

Sorry, no idea why the colour-highlighting is screwed there :frowning:


#4

we only want to the rows WHERE Attributes = 72 OR Attributes = 65536 OR Attributes = 104


#5

You need to add a WHERE clause then, otherwise it will do all rows in the table. There is NO correlation between your SELECT and the following UPDATE, so although the SELECT has a WHERE clause to restrict the output, the UPDATE does not and will thus update all rows in the table. If you have run it? that will already have happened.

update dbo.Volumes
set ...
WHERE Attributes = 72 OR Attributes = 65536 OR Attributes = 104

Please note my earlier comments about your use of REPLACE, which in effect doesn't do any actual replace, so will prefix all (matched) rows with '\sufstor02.suffel-gmbh.de\CD'


#6

thank you for your input. We did run the following original query and it overwrote all rows in the table (which we did not want)

update dbo.Volumes
set path = REPLACE ('\suffelstorage01.suffel-gmbh.de', '\suffelstorage01.suffel-gmbh.de', '\sufstor02.suffel-gmbh.de\CD') + VolumeName;

The way we understood the REPLACE command is that it would only replace if the string matched i.e.. (string to search for, portion of string to replace, replace string with)

We do not understand why it overwrote all rows in the table?

Next, we will now try it like this:

update dbo.Volumes
set path = '\sufstor02.suffel-gmbh.de\CD' + VolumeName;
WHERE Attributes = 72 OR Attributes = 65536 OR Attributes = 104

Is there a way to run this to see the results before we UPDATE the table.

Thank you for your time.


#7
SELECT 
--UPDATE SET 
   path = '\sufstor02.suffel-gmbh.de\CD\' + VolumeName
FROM dbo.Volumes  
WHERE Attributes = 72 OR Attributes = 65536 OR Attributes = 104;

Notice that your code has a semi colon after ValumeName which means the where clause is not seen.


#8

What you probably meant was:

REPLACE(YourColumnNameHere, '\suffelstorage01.suffel-gmbh.de\', '\sufstor02.suffel-gmbh.de\CD\')

My suggestion would be that you use syntax like this:

BEGIN TRANSACTION

SELECT TOP 100 *
FROM dbo.Volumes
WHERE Attributes = 72 OR Attributes = 65536 OR Attributes = 104

update dbo.Volumes
set path = ...
WHERE Attributes = 72 OR Attributes = 65536 OR Attributes = 104

SELECT TOP 100 *
FROM dbo.Volumes
WHERE Attributes = 72 OR Attributes = 65536 OR Attributes = 104

-- COMMIT
-- ROLLBACK

This will show you some (100) rows before the update, and after the update (you should add an ORDER BY on the Primary Key to get the SAME 100 rows :slight_smile: ) and then you can compare and see how they look.

if you are happy then highlight and execute the "COMMIT" otherwise do the "ROLLBACK" instead.

Note that the table, or part of it, may be locked and therefore time is of the essence to review and perform the Commit or Rollback, otherwise other users may be blocked or timeout. Of course if you are doing this during downtime that won;'t be a problem.


#9

I suspect the code below is what you really want to do. Change paths with those attributes but only if they contain the string that needs to be replaced:

UPDATE dbo.Volumes
SET Path = REPLACE(Path, '\suffelstorage01.suffel-gmbh.de\', '\sufstor02.suffel-gmbh.de\CD\') + VolumeName
WHERE Attributes IN ( 72, 104, 65536 ) AND 
    Path LIKE '%\suffelstorage01.suffel-gmbh.de\%'