replace values in a query result

[George N]

I have written a query that gets data from 2 related tables the query is as follows :

SELECT dbo.[Document].Id, dbo.[Document].Name, dbo.[Document].Format, dbo.DocumentProperty.FileId, dbo.DocumentProperty.Type
FROM         dbo.[Document] INNER JOIN
                      dbo.DocumentProperty ON dbo.[Document].Id = dbo.DocumentProperty.FileId
WHERE     (dbo.[Document].Format = 0 )

and the result is :

id|    Name    |Format|Type
1 |1-1-2010.pdf|  0   |4
2 |2-1-1990.pdf|  0   |4

and so on

the thing is that want to replace the type to 3 when the foramt is 0

I made this do far :

set Type = replace(Type, '4' , '3' ) WHERE Format = 0

the type is in dbo.documentpro perty table and the format is in document table

but what do I have to update ? table ? query ?
help will be great

[gpl]

Im not entirely sure whether you want to just change the value returned in the query, or you want to upodate the column on the database. It would appear from the end of your post that you wish to update the table, so try this

UPDATE dbo.[Document]
SET Format=3
FROM
dbo.[Document] INNER JOIN
dbo.DocumentProperty ON dbo.[Document].Id = dbo.DocumentProperty.FileId
WHERE (dbo.[Document].Format = 0 )

[NeoPa]

George, an important question here is :
Do you require the [Type] data to be changed to 3 in the table, or do you simply want it to display as 3 in your query?

If the former then GPL's SQL is only slightly different from what I'd propose :

UPDATE dbo.[Document]
SET    [Type]=3
FROM   dbo.[Document]
       INNER JOIN
       dbo.DocumentProperty
  ON   dbo.[Document].Id = dbo.DocumentProperty.FileId
WHERE  (dbo.[Document].Format = 0)

If the latter then your SQL would be :

SELECT tD.Id
     , tD.Name
     , tD.Format
     , tDP.FileId
     , 3 AS [Type]
FROM   dbo.[Document] AS tD
       INNER JOIN
       dbo.DocumentProperty AS tDP
  ON   tD.Id = tDP.FileId
WHERE  (tD.Format = 0)

I'm sure you will see from this how important it is to express your question clearly in the first post, so that the experts know what it is they are trying to answer.

[gpl]

oops, copy & paste error, will teach me to double-check my post in future

[NeoPa]

Not a worry. If that is indeed what the OP is after I'm sure they would have spotted it when they tried to use it anyway.