|
 |
asp_databases thread: RE: [asp_databases]Ken::: Re: "sql syntax problem"
Message #1 by "Taher Moiyed" <taherm@f...> on Wed, 22 Aug 2001 09:57:17 +0100
|
|
UPDATE stock INNER JOIN prod ON [stock].[itemcode]=[prod].hkey SET
stock.itemcode = [prod].hkey;
now i have written the above query in my sql server query analyzer and it
says that
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'INNER'.
i will greatly appreciate your help
-----Original Message-----
From: Ken Schaefer [mailto:ken@a...]
Sent: 22 August 2001 03:02
To: ASP Databases
Subject: [asp_databases] Re: "sql syntax problem"
AFAIK NOT EXISTS is the better way to do this.
Drew, you're right - it does stop when it finds the first match - but for
each record in the "outer" query. IE, for each record in the outer query, it
stops evaluating the inner query as soon as it finds a match.
Cheers
Ken
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
----- Original Message -----
From: "Drew, Ron" <RDrew@B...>
To: "ASP Databases" <asp_databases@p...>
Sent: Wednesday, August 22, 2001 4:36 AM
Subject: [asp_databases] Re: "sql syntax problem"
I just responded with "not in"....doesn't not exists stop at the first
itemcode not in prod
-----Original Message-----
From: Matteo Lonardi [mailto:mlonardi@h...]
Sent: Tuesday, August 21, 2001 7:57 AM
To: ASP Databases
Subject: [asp_databases] Re: "sql syntax problem"
Try:
Select * From stock Where not exists (
SELECT stock.itemcode
FROM prod right outer JOIN stock ON prod.Key = stock.itemcode)
----- Original Message -----
From: <taherm@f...>
To: "ASP Databases" <asp_databases@p...>
Sent: Tuesday, August 21, 2001 1:35 PM
Subject: [asp_databases] "sql syntax problem"
> SELECT stock.itemcode
> FROM prod right outer JOIN stock ON prod.Key = stock.itemcode;
>
> the above sql syntax retrieves all the records which are present in prod
> and stock table but ignore any records which are present in stock but not
> in prod.
> for example if prod has 2 records called a and b
> stock has a , b, and c
> this query outputs only a and b
>
> what i need is to output only c.
> in other words i need the sql syntax to check in prod and stock table and
> out only those from stock whih is not present in prod.
>
> Its really very very urgent. can anyone help me.
> thanks
Message #2 by "Drew, Ron" <RDrew@B...> on Wed, 22 Aug 2001 08:35:49 -0400
|
|
Not sure this makes sense to me. Looks like you are trying to set the
stock.itemcode to equal prod.hkey when they already match. Syntax is wrong,
but first need to define what you are trying to do first.
-----Original Message-----
From: Taher Moiyed [mailto:taherm@f...]
Sent: Wednesday, August 22, 2001 4:57 AM
To: ASP Databases
Subject: [asp_databases] RE: [asp_databases]Ken::: Re: "sql syntax
problem"
UPDATE stock INNER JOIN prod ON [stock].[itemcode]=[prod].hkey SET
stock.itemcode = [prod].hkey;
now i have written the above query in my sql server query analyzer and it
says that
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'INNER'.
i will greatly appreciate your help
-----Original Message-----
From: Ken Schaefer [mailto:ken@a...]
Sent: 22 August 2001 03:02
To: ASP Databases
Subject: [asp_databases] Re: "sql syntax problem"
AFAIK NOT EXISTS is the better way to do this.
Drew, you're right - it does stop when it finds the first match - but for
each record in the "outer" query. IE, for each record in the outer query,
it stops evaluating the inner query as soon as it finds a match.
Cheers
Ken
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
----- Original Message -----
From: "Drew, Ron" <RDrew@B...>
To: "ASP Databases" <asp_databases@p...>
Sent: Wednesday, August 22, 2001 4:36 AM
Subject: [asp_databases] Re: "sql syntax problem"
I just responded with "not in"....doesn't not exists stop at the first
itemcode not in prod
-----Original Message-----
From: Matteo Lonardi [mailto:mlonardi@h...]
Sent: Tuesday, August 21, 2001 7:57 AM
To: ASP Databases
Subject: [asp_databases] Re: "sql syntax problem"
Try:
Select * From stock Where not exists (
SELECT stock.itemcode
FROM prod right outer JOIN stock ON prod.Key =3D stock.itemcode)
----- Original Message -----
From: <taherm@f...>
To: "ASP Databases" <asp_databases@p...>
Sent: Tuesday, August 21, 2001 1:35 PM
Subject: [asp_databases] "sql syntax problem"
> SELECT stock.itemcode
> FROM prod right outer JOIN stock ON prod.Key = stock.itemcode;
>
> the above sql syntax retrieves all the records which are present in prod
> and stock table but ignore any records which are present in stock but not
> in prod.
> for example if prod has 2 records called a and b
> stock has a , b, and c
> this query outputs only a and b
>
> what i need is to output only c.
> in other words i need the sql syntax to check in prod and stock table
> and out only those from stock whih is not present in prod.
>
> Its really very very urgent. can anyone help me.
> thanks
Message #3 by David Cameron <dcameron@i...> on Thu, 23 Aug 2001 09:28:28 +1000
|
|
UPDATE syntax goes like this:
UPDATE <table name> SET <field name> =3D <value> WHERE <conditions>
You cannot make an join to another table. If you omit the WHERE clause
every
field in the table will be updated. Normally you tend to use the
primary key
for a table when you want to update one row.
UPDATE !=3D SELECT
regards
David Cameron
nOw.b2b
dcameron@i...
-----Original Message-----
From: Drew, Ron [mailto:RDrew@B...]
Sent: Wednesday, 22 August 2001 10:36 PM
To: ASP Databases
Subject: [asp_databases] RE: [asp_databases]Ken::: Re: "sql syntax pro
blem"
Not sure this makes sense to me. Looks like you are trying to set the
stock.itemcode to equal prod.hkey when they already match. Syntax is
wrong,
but first need to define what you are trying to do first.
-----Original Message-----
From: Taher Moiyed [mailto:taherm@f...]
Sent: Wednesday, August 22, 2001 4:57 AM
To: ASP Databases
Subject: [asp_databases] RE: [asp_databases]Ken::: Re: "sql syntax
problem"
UPDATE stock INNER JOIN prod ON [stock].[itemcode]=3D[prod].hkey SET
stock.itemcode =3D [prod].hkey;
now i have written the above query in my sql server query analyzer and
it
says that
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'INNER'.
i will greatly appreciate your help
-----Original Message-----
From: Ken Schaefer [mailto:ken@a...]
Sent: 22 August 2001 03:02
To: ASP Databases
Subject: [asp_databases] Re: "sql syntax problem"
AFAIK NOT EXISTS is the better way to do this.
Drew, you're right - it does stop when it finds the first match - but
for
each record in the "outer" query. IE, for each record in the outer
query,
it stops evaluating the inner query as soon as it finds a match.
Cheers
Ken
|
|
|
View Cart
