sql server new users: Updating one table with data from another

Updating one table with data from another - problem

Ernie
1/26/2007 6:26:35 AM

sql server new users: I've searched the posts and found some useful code on updating one
table with data from another however, when I created the following SQL
statement, I get an error 'ADO error: The column prefix
'TAinvxSUBstatusNEW' does not match with a table name or alias name
used in the query'

Both tables and associated field values have been verified. Is there
some other syntax that I am missing?

UPDATE dbo.TUmstrSHPbu
SET CBshipSITEdate = TAinvxSUBstatusNEW.LogDate
WHERE (ShipIDshp = TAinvxSUBstatusNEW.ShipIDinv)

Thanks.

Ernie

Re: Updating one table with data from another - problem

Andrew J. Kelly
1/26/2007 11:04:29 AM

You have to inform SQL Server of the existance of the other table. You have
two choices. If you are only updating a single column from another talble
you canuse a subselect.

UPDATE dbo.TUmstrSHPbu
SET CBshipSITEdate = (SELECT b.LogDate FROM TAinvxSUBstatusNEW AS b
WHERE b.ShipIDinv = dbo.TUmstrSHPbu.ShipIDshp )

Or you can use the UPDATE with a join

UPDATE a
SET CBshipSITEdate = TAinvxSUBstatusNEW.LogDate
FROM dbo.TUmstrSHPbu AS a INNER JOIN TAinvxSUBstatusNEW as b
ON a.ShipIDshp = b.ShipIDinv

--
Andrew J. Kelly SQL MVP

[quoted text, click to view]