Prev: SQL Server 2005 DB Roles
Next: Adding Statistics
From: vvenk on 17 Mar 2010 17:05
Hello:

I have two tables:

Table_A:
ID
Name_A
Name_B
Update_DT

Table_B
ID
Name_B
Update_DT

Table_A.Name_A is a foreign key into Table_B.ID

I want to udpate Table_A.Name_B on the matching Table_B.Name_B

I tried the following SQL that fails:

UPDATE TABLE_A
SET Name_B = TABLE_B.Name_B, Update_DT = B.Update_DT
INNER JOIN TABLE_B ON Table_A.Name_a = Table_B.ID

What am I doing wrong?

Venki
From: vvenk on 17 Mar 2010 17:16
Sorry:

The code I have is:

UPDATE A
SET Name_B = B.Name_B
FROM TABLE_A A
INNER JOIN TABLE_B B ON A.Name_a = B.ID

Thanks.

Venki
From: Erland Sommarskog on 17 Mar 2010 19:06
vvenk (vvenk(a)discussions.microsoft.com) writes:
> Hello:
>
> I have two tables:
>
> Table_A:
> ID
> Name_A
> Name_B
> Update_DT
>
> Table_B
> ID
> Name_B
> Update_DT
>
> Table_A.Name_A is a foreign key into Table_B.ID
>
> I want to udpate Table_A.Name_B on the matching Table_B.Name_B
>
> I tried the following SQL that fails:
>
> UPDATE TABLE_A
> SET Name_B = TABLE_B.Name_B, Update_DT = B.Update_DT
> INNER JOIN TABLE_B ON Table_A.Name_a = Table_B.ID
>
> What am I doing wrong?

Beside the error of posting the wrong query, the other mistake you
do is that you don't tell us what "fails" mean. Do you get an error
message? Do you get an unexpected result? Does the ceiling fall down
on you?


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: bill on 17 Mar 2010 22:02
How about this:

UPDATE TABLE_A
SET Name_B =
(
SELECT Name_B
FROM TABLE_B
WHERE
TABLE_A.Name_B = TABLE_B.ID
)


This statement will update TABLE_A.Name_B, to the correct value if
there is a match. If there is no match, it will set TABLE_A.Name_B to
NULL. That is probably the behavior you want in this case. If you
_don't_ want the column set to NULL on no match, then add an EXISTS
clause like this:

AND EXISTS
(
SELECT
*
FROM
TABLE_B
WHERE
TABLE_B.ID = TABLE_A.Name_B
)


As an aside, I think the column names are quite confusing. It seems
counter-intuitive for a Name to be foreign key to an ID. Speaking of
which, ID (and the identity property) is generally radically over-
used. You should always at least identify the natural key and put a
unique constraint over it. Generally, you might as well just use the
natural key, and leave out the ID altogether. Natural keys allow you
to enforce business rules through the key structure alone that would
otherwise require quite a bit of code.

Thanks,

Bill

From: Uri Dimant on 18 Mar 2010 02:57
I like this one
WITH cte
AS
(
SELECT A.Name_B AS Dest , B.Name_B AS Source
FROM TABLE_A A
INNER JOIN TABLE_B B ON A.Name_a = B.ID
) UPDATE cte SET Dest =Source



"vvenk" <vvenk(a)discussions.microsoft.com> wrote in message
news:A85CF129-96D7-4769-9B29-BB473C7A6B90(a)microsoft.com...
> Sorry:
>
> The code I have is:
>
> UPDATE A
> SET Name_B = B.Name_B
> FROM TABLE_A A
> INNER JOIN TABLE_B B ON A.Name_a = B.ID
>
> Thanks.
>
> Venki


 |  Next  |  Last
Pages: 1 2
Prev: SQL Server 2005 DB Roles
Next: Adding Statistics