converting access to sqlserver 7

I have a query which fixes area codes. it runs:
UPDATE (nocall INNER JOIN pildta ON (nocall.SUFF = pildta.suff) AND (nocall.PREF = pildta.preff) AND (nocall.AREA = pildta.area)) INNER JOIN ZipCodeUS ON pildta.Zip = ZipCodeUS.ZIP SET nocall.AREA = [a/c]
WHERE (((IIf([nocall]![area]<>[a/c],1,0))=1));

I managed to replace the IIF with a CASE for displaying the unmatched tables like this:
SELECT dbo.nocall.AREA, dbo.pildta.Zip, dbo.ZipCodeUS.[A/C], case when [a/c]=[nocall].[area] then 1 else 0 end as match
FROM dbo.pildta INNER JOIN
dbo.ZipCodeUS ON dbo.pildta.Zip = dbo.ZipCodeUS.ZIP INNER JOIN
dbo.nocall ON dbo.pildta.area = dbo.nocall.AREA AND dbo.pildta.preff = dbo.nocall.PREF AND dbo.pildta.suff = dbo.nocall.SUFF

How can I make this an update query based on [match] having a value of 1?How about moving the [a/c]=[nocall].[area] from a CASE clause to a WHERE clause (which should limit the results to records that would return a 1 in your CASE clause), and then making that query a subquery to your UPDATE command? E.g., UPDATE table SET column = value WHERE tableprimarykey IN (subquery).|||ok. What am I doing wrong. I'm now trying this:
UPDATE nocall
SET AREA = [a/c] IN (SELECT ZipCodeUS.[A/C] FROM ZipCodeUS INNER JOIN pildta ON ZipCodeUS.ZIP = pildta.Zip INNER JOIN nocall ON pildta.area = nocall.AREA AND pildta.preff = nocall.PREF AND pildta.suff = nocall.SUFF)
WHERE [ctr]=[ctr] IN (SELECT nocall.AREA, ZipCodeUS.[A/C], nocall.ctr FROM ZipCodeUS INNER JOIN pildta ON ZipCodeUS.ZIP = pildta.Zip INNER JOIN nocall ON pildta.area = nocall.AREA AND pildta.preff = nocall.PREF AND pildta.suff = nocall.SUFF AND ZipCodeUS.[A/C] <> nocall.AREA)

When i try to save, I get incorrect syntax near the keyword IN, incorrect syntax near the word WHERE|||Well, the first error in the query in your last post is "SET AREA = [a/c] IN (..." This would have to be like "SET AREA = [a/c] WHERE AREA IN (..."

I'm not sure I understand what the query is to accomplish well enough to give you complete rewrite, so tell me if this is correct:

Your goal is to replace values in NoCall.Area with values from ZipCodeUS.[A/C] when and only when:

1. NoCall.Area does not already equal ZipCodeUS.[A/C]
AND
2. ZipCodeUS has a matching record in Pildta based on .Zip
AND
3. NoCall has a record that matches Pildta's Area, Preff, and Suff

Convert SQL query to SP

I have the following sql query

select A,B,sum(C),D

from Y inner join X on..inner join Z on...

where A like ..., B between ...

group by A,B,D

as a result of this select I get list of A,B,sum(C),D values

Ho do I create a SP that updates some table W with list of selected A,B,sum(C),D values ?

Should I use cursor for this or not?

Thank you!!!!!

Enclose your update statement with Create Proc...

No need to have cursor to update your values (use Update...From syntax)

here the sample,

Code Snippet

Create proc YourSpname

as

Update sometable

set

sometablecol = sumofc

From

sometable main

Join

(

select A,B,D,sum(c) as sumofc

from Y inner join X on..inner join Z on...

where A like ..., B between ...

group by A,B,D

) as data

On data.a = main.a and data.b=main.b and data.d=main.d