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 ... ?
>
>

---

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:
> 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/techinfo/productdoc/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...
> 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:
> 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:
> FIRST is an Access thing. The closest thing in T-SQL is
Interesting...
> 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 ?
> 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 ... ?
> SK
> Guido Stepken wrote:
>> 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
>|||Guido Stepken wrote:
> Steve Kass wrote:
>> FIRST is an Access thing. The closest thing in T-SQL is
>
> Interesting...
>> 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 ?
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
>> 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 ... ?
>> SK
>> Guido Stepken wrote:
>> 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
>>
>

Problem with XQuery Syntax

I'm storing addresses in an XML address column, and I need to be able
to do partial searches...
For example...
declare @.tAddress table (Address xml DEFAULT '<Address Record />' )
INSERT INTO @.tAddress (Address)
VALUES ('<AddressType name="Shipping">
<AddressRecord StreetLine1="111 Main"
City="Houston"
State="TX"
ZipCode = "11111" />
</AddressType> ');
A single address could have multiple address types (billing and
shipping) and multiple entries for each one.
I know how to query for an exact match on a column...
SELECT Address FROM @.tAddress WHERE
Address.exist('/AddressType/AddressRecord[ @.City = "Houston" ]') =1
However...I'm getting errors when I try to use a contains (in the
same way I'd use a City Like '%ous%'
How would I code a contains on any particular column?
Thanks!
KevinFollow-up...
I tried the following:
SELECT * FROM @.tAddress
where
address.exist('/AddressType/AddressRecord/City/text()[contains(.,"Houston")]
')
= 1
It doesn't bomb, but it generates zero results.
I'm sure I'm missing something, but don't know what.
Kevin|||Try this
SELECT * FROM @.tAddress
where
address.exist('/AddressType/AddressRecord [contains(@.City,"Houston")]')
= 1|||On 10 Mar 2006 12:08:09 -0800, markc600@.hotmail.com wrote:

>Try this
>SELECT * FROM @.tAddress
>where
>address.exist('/AddressType/AddressRecord [contains(@.City,"Houston")]')
>= 1
THAT WORKED!!!!!
You're the man!!!!!!!!!!!
THANK YOU!!!!!!!
Kevin