Problem: What's that sql script doing ?

Hello !
Due to a lack of SQL server i am wondering, what that sql script is
doing. It it is part of a VB macro.
UPDATE address AS A SET ZIP=(SELECT FIRST(ZIP) FROM address WHERE
ZIP<>A.ZIP)
tnx in advance, Guido StepkenGuido Stepken (stepken@.little-idiot.de) writes:

quote:

---

> Due to a lack of SQL server i am wondering, what that sql script is
> doing. It it is part of a VB macro.
> UPDATE address AS A SET ZIP=(SELECT FIRST(ZIP) FROM address WHERE
> ZIP<>A.ZIP)

---

I guess it depends on which engine you run it on. On SQL Server this
happens:
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'AS'.
Server: Msg 195, Level 15, State 1, Line 1
'FIRST' is not a recognized function name.
I would guess that it intended for Access.
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||There is no such concept as "FIRST" in SQL Server. Did you mean MIN() or
MAX()? Or something else altogether?
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Guido Stepken" <stepken@.little-idiot.de> wrote in message
news:bts9rc$umu$06$1@.news.t-online.com...

quote:

---

> Hello !
> Due to a lack of SQL server i am wondering, what that sql script is
> doing. It it is part of a VB macro.
> UPDATE address AS A SET ZIP=(SELECT FIRST(ZIP) FROM address WHERE
> ZIP<>A.ZIP)
> tnx in advance, Guido Stepken
>

---

|||FIRST is an Access thing. The closest thing in T-SQL is
UPDATE address SET
ZIP = (
SELECT TOP 1 ZIP
FROM address
WHERE ZIP <> A.ZIP
)
But TOP 1 without ORDER BY is not often a good idea, and this is a
rather bizarre update query: change every ZIP in the address table to
some random other ZIP?
SK
Guido Stepken wrote:

quote:

---

> Hello !
> Due to a lack of SQL server i am wondering, what that sql script is
> doing. It it is part of a VB macro.
> UPDATE address AS A SET ZIP=(SELECT FIRST(ZIP) FROM address WHERE
> ZIP<>A.ZIP)
> tnx in advance, Guido Stepken
>

---

|||Steve Kass wrote:

quote:

---

> FIRST is an Access thing. The closest thing in T-SQL is

---

Interesting...

quote:

---

> UPDATE address SET
> ZIP = (
> SELECT TOP 1 ZIP
> FROM address
> WHERE ZIP <> A.ZIP
> )
>

---

You rather mean:
UPDATE address AS A SET
ZIP = (
SELECT TOP 1 ZIP
FROM address
WHERE ZIP <> A.ZIP)
does it work on T-SQL ?

quote:

---

> But TOP 1 without ORDER BY is not often a good idea, and this is a
> rather bizarre update query: change every ZIP in the address table to
> some random other ZIP?

---

Bizarre ... a SQL attack on data warehouse, ERP, CRM, - programs,
address books, outlook ... ?

quote:

---

> SK
> Guido Stepken wrote:
>
>

---

|||Guido Stepken wrote:

quote:

---

> Steve Kass wrote:
>
>
> Interesting...
>
> You rather mean:
> UPDATE address AS A SET
> ZIP = (
> SELECT TOP 1 ZIP
> FROM address
> WHERE ZIP <> A.ZIP)
> does it work on T-SQL ?

---

Guido,
T-SQL does not support an alias on the target table of an UPDATE
query, and I should have written
UPDATE address SET
ZIP = (
SELECT TOP 1 ZIP
FROM address A
WHERE A.ZIP <> ZIP
)
SK

quote:

---

>
>
> Bizarre ... a SQL attack on data warehouse, ERP, CRM, - programs,
> address books, outlook ... ?
>
>

---