Newbie needs help with SQL statement

Hello,

I'm having a difficult time finding the right sql syntax to perform an
update. Here is the situation:

I have two tables, each with an orderid field and a removal_date field.
There is a one-to-many relationship between table A and table B, with B
having multiple records to each one in table A, related by the orderid
field. Table A's primary key is the orderid field, and is the only table
that has data in the removal_date field. I would like to update the
removal_date field in table B with the values of the removal_date field in
table A.

Can this be done with a single sql statement? Right now I'm using a VB
program to build the update sql, but this is cumbersome. Any help would be
appreciated.

Thanks.You would need to check this, but I think it would work.

update TableB
set TableB.removal_date = TableA.removal_date
from Tableb join TableA on TableB.orderid = TableA.orderid

"George J" <gjewell@.houston.rr.com> wrote in message
news:Sxn6d.31407$W21.29433@.fe2.texas.rr.com...
> Hello,
> I'm having a difficult time finding the right sql syntax to perform an
> update. Here is the situation:
> I have two tables, each with an orderid field and a removal_date field.
> There is a one-to-many relationship between table A and table B, with B
> having multiple records to each one in table A, related by the orderid
> field. Table A's primary key is the orderid field, and is the only table
> that has data in the removal_date field. I would like to update the
> removal_date field in table B with the values of the removal_date field in
> table A.
> Can this be done with a single sql statement? Right now I'm using a VB
> program to build the update sql, but this is cumbersome. Any help would be
> appreciated.
> Thanks.|||On Wed, 29 Sep 2004 00:42:26 GMT, George J wrote:

>Hello,
>I'm having a difficult time finding the right sql syntax to perform an
>update. Here is the situation:
>I have two tables, each with an orderid field and a removal_date field.
>There is a one-to-many relationship between table A and table B, with B
>having multiple records to each one in table A, related by the orderid
>field. Table A's primary key is the orderid field, and is the only table
>that has data in the removal_date field. I would like to update the
>removal_date field in table B with the values of the removal_date field in
>table A.
>Can this be done with a single sql statement? Right now I'm using a VB
>program to build the update sql, but this is cumbersome. Any help would be
>appreciated.
>Thanks.

Hi George,

As an alternative to Oscar's suggestion:

UPDATE tableB
SET removal_date = (SELECT removal_date
FROM tableA
WHERE tableA.orderid = tableB.orderid)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Newbie help with dynamic SQL

I am creating a stored procedure that will perform a search against a
table. I am passing search parameters to the SP. If the user did not
select a value on the front end, then I am passing NULL into the SP for
that field. So my question is, what is the best practice to only search
on a field if a value is passed for the given field?

This is what I was thinking, but obviously this doesn't work:

@.vchrFieldOne VARCHAR (200) = NULL

SELECT ...
FROM ...
WHERE
0=0

AND
CASE @.vchrFieldOne
WHEN NULL THEN (0 = 0)
ELSE (vchrFieldOne = @.vchrFieldOne)
ENDMine would look something like this:

CREATE PROC pub_info2 @.vchrFieldOne varchar(200) = NULL
AS
If (@.vchrFieldOne is null)
BEGIN
-- select statement where value is not given
END
ELSE
BEGIN
-- select statement where value is given
END

Hope it helps,
Tony Sebion

"Erich93063" <erich93063@.gmail.com> wrote in message
news:1123175211.631764.144360@.z14g2000cwz.googlegr oups.com:

> I am creating a stored procedure that will perform a search against a
> table. I am passing search parameters to the SP. If the user did not
> select a value on the front end, then I am passing NULL into the SP for
> that field. So my question is, what is the best practice to only search
> on a field if a value is passed for the given field?
> This is what I was thinking, but obviously this doesn't work:
> @.vchrFieldOne VARCHAR (200) = NULL
> SELECT ...
> FROM ...
> WHERE
> 0=0
> AND
> CASE @.vchrFieldOne
> WHEN NULL THEN (0 = 0)
> ELSE (vchrFieldOne = @.vchrFieldOne)
> END|||Erich93063 (erich93063@.gmail.com) writes:
> I am creating a stored procedure that will perform a search against a
> table. I am passing search parameters to the SP. If the user did not
> select a value on the front end, then I am passing NULL into the SP for
> that field. So my question is, what is the best practice to only search
> on a field if a value is passed for the given field?
> This is what I was thinking, but obviously this doesn't work:
> @.vchrFieldOne VARCHAR (200) = NULL
> SELECT ...
> FROM ...
> WHERE
> 0=0
> AND
> CASE @.vchrFieldOne
> WHEN NULL THEN (0 = 0)
> ELSE (vchrFieldOne = @.vchrFieldOne)
> END

It doesn't work for several reasons.

1) WHEN NULL - is the same as "WHEN @.charFieldOne = NULL", but in SQL
NULL is never equal to anything, not even another NULL. NULL is a
unknown value, and any comparison with NULL yields the value UNKNOWN.
Correct is WHEN @.vcharFieldOne IS NULL.
2) The return value of a CASE expresssion is always an SQL Server data
type, and there is no boolean data type in T-SQL. Thus you cannot
have "THEN (0 = 9".

The normal way to write this is

AND (vchrFieldOne = @.vchrFieldOne OR @.vchrFieldOne IS NULL)

However, while this works as far as giving the correct result, the
performance can be unbearable. I have an article on my web site that
discusses a number of alternatives for dynamic searches,
http://www.sommarskog.se/dyn-search.html.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks for the reply. I tried it (At least what I think you meant) but
I get an error (Incorrect syntax near the keyword 'AND' and Incorrect
syntax near the keyword 'END'. Here is what I have:

SELECT ...
FROM...
WHERE 0=0

IF (@.vchrFieldOne IS NULL)

BEGIN
AND 0=0
END
ELSE
BEGIN
AND vchrFieldOne = @.vchrFieldOne
END|||Nice article. Thanks for sharing it.|||YES, I read your article and used your example for dynamic SQL and it
works PERFECTLY. THANKS!!!!!!!!!!!!!