Simple Update SQL - Error Out

[ank2go]

Hi,

Please help.

I have a simple update sql:

UPDATE dbo.tbl01
SET par = 'Y'

FROM dbo.tbl01 tbl01
INNER JOIN
( SELECT name
FROM dbo.tbl01
WHERE recordtype = 'P' and active = 'Y' and par = 'Y' ) ppr ON tbl01.name = ppr.name

WHERE tbl01.recordtype <> 'P' and tbl01.active = 'Y'


the error message is:

The column prefix 'ppr' does not match with a table name or alias name used in the query.

But when I do this it doesn't error out:

SELECT *
FROM dbo.tbl01 tbl01
INNER JOIN
( SELECT name
FROM dbo.tbl01
WHERE recordtype = 'P' and active = 'Y' and par = 'Y' ) ppr ON tbl01.name = ppr.name

WHERE tbl01.recordtype <> 'P' and tbl01.active = 'Y'

Any ideas? and Thanks !

 

[JarlH]

The UPDATE FROM statement is Microsoft(?) specific, and not included in the ISO/ANSI SQL standard. Quite confusing syntax I must say.

What are you trying to do?

You could try something like
UPDATE table
SET COLUMN = (SELECT ...)
WHERE a = b

 

[PHV]

Something like this ?
UPDATE dbo.tbl01
SET par = 'Y'
WHERE name IN (
SELECT name FROM dbo.tbl01 tbl01 INNER JOIN
(SELECT name FROM dbo.tbl01
WHERE recordtype = 'P' and active = 'Y' and par = 'Y'
) ppr ON tbl01.name = ppr.name
WHERE tbl01.recordtype <> 'P' and tbl01.active = 'Y'
)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ank2go]

JarlH, it's ansi 92 standard, right? It's for a MS SQL box.

PHV, it looks like I'll try the where name in approach.

Thanks for the replies !