query no longer working?

UPDATE PUBLIC_Transaction INNER JOIN ShipTo ON PUBLIC_Transaction.ShipToID =
ShipTo.ID SET PUBLIC_Transaction.ReferenceNumber = [dbo_ShipTo].[Name] WHERE
(((Len([name]))>0))
When I try to run this I get the error message
An Error occurred while executing query: Inccorrect syntax near the keyword
‘inner’
We used to have local support that set this up we were able to run it once
when it was set up and was told we could re run it when ever we needed to
update the infor. Also was told they would send me a new query that would
update this info constantly. Never got the email they disappeared along with
$1000.00 in pre paid support.
Original goal was under a specific customer in the purchase history tab to
have the Customer name from the ship to tab appear in the Reference # field
in the purchase history tab
Any help would be appreciatedHi
Did you ever get this query working?
(untested)
UPDATE PUBLIC_PUBLIC_Transaction SET ReferenceNumber=(SELECT
[dbo_ShipTo].[Name]
FROM ShipTo JOIN PUBLIC_Transaction ON PUBLIC_Transaction.ShipToID =
ShipTo.ID WHERE Len([name])>0 )
WHERE EXISTS (SELECT * FROM ShipTo JOIN PUBLIC_Transaction ON
PUBLIC_Transaction.ShipToID =
ShipTo.ID WHERE Len([name])>0)
"shoeman240" <shoeman240@.discussions.microsoft.com> wrote in message
news:CA97C711-EFD2-49A1-9C08-3EDC81DB4CE4@.microsoft.com...
> UPDATE PUBLIC_Transaction INNER JOIN ShipTo ON PUBLIC_Transaction.ShipToID
> =
> ShipTo.ID SET PUBLIC_Transaction.ReferenceNumber = [dbo_ShipTo].[Name]
> WHERE
> (((Len([name]))>0))
>
> When I try to run this I get the error message
> An Error occurred while executing query: Inccorrect syntax near the
> keyword
> inner
> We used to have local support that set this up we were able to run it once
> when it was set up and was told we could re run it when ever we needed to
> update the infor. Also was told they would send me a new query that would
> update this info constantly. Never got the email they disappeared along
> with
> $1000.00 in pre paid support.
> Original goal was under a specific customer in the purchase history tab to
> have the Customer name from the ship to tab appear in the Reference #
> field
> in the purchase history tab
> Any help would be appreciated
>|||Hello,
This query never worked in SQL Server (it probably worked in Microsoft
Access). An equivalent T-SQL query would be:
UPDATE PUBLIC_Transaction
SET ReferenceNumber = ShipTo.Name
FROM PUBLIC_Transaction INNER JOIN ShipTo
ON PUBLIC_Transaction.ShipToID = ShipTo.ID
WHERE Len(name)>0
And another equivalent query (in standard ANSI-SQL) is:
UPDATE PUBLIC_Transaction
SET ReferenceNumber = (
SELECT Name FROM ShipTo
WHERE PUBLIC_Transaction.ShipToID = ShipTo.ID
AND Len(name)>0
) WHERE EXISTS (
SELECT * FROM ShipTo
WHERE PUBLIC_Transaction.ShipToID = ShipTo.ID
AND Len(name)>0
)
Razvan