September 21, 2007 at 1:38 am
Hi All,
I'm attempting to update a table from one database to another table in a different database on the same server. I'm not sure if this is the optimal way of doing this. Any advice would be appreciated. Here is the code:
USE
[TEST]
GO
SET
ANSI_NULLS ON
GO
SET
QUOTED_IDENTIFIER ON
GO
alter
PROCedure [dbo].[usp_wb_ptupd]
AS
UPDATE
Waybill.dbo.Cabinet
SET
Origin_ID = TBLHIS_WAYBILL.Origin_ID,
Destination_ID
= Test.dbo.TBLHIS_WAYBILL.Destination_ID,
Debit_Account_No = Test.dbo.TBLHIS_WAYBILL.Debit_Account_No,
Workticket_No = Test.dbo.TBLHIS_WAYBILL.Workticket_No,
FROM
FMSJHBTest.dbo.TBLHIS_WAYBILL
WHERE
((Test.dbo.TBLHIS_WAYBILL.POD_RECEIVED IS NULL) OR (Test.dbo.TBLHIS_WAYBILL.POD_RECEIVED = 0))
AND
Test.dbo.TBLHIS_WAYBILL.Waybill_No = Waybill.dbo.Cabinet.Waybill_No COLLATE Latin1_General_CI_AS;
Go
/* Flag all maching columns as having been dealt with */
UPDATE
Test.dbo.TBLHIS_WAYBILL
SET
POD_RECEIVED
= 1
POD_DATE_REC
= Waybill.dbo.Cabinet.Created
FROM
Waybill.dbo.Cabinet
WHERE
((Test.dbo.TBLHIS_WAYBILL.POD_DATE_REC IS NULL) OR (Test.dbo.TBLHIS_WAYBILL.POD_DATE_REC = 0)) AND
Test
.dbo.TBLHIS_WAYBILL.Waybill_No = Waybill.dbo.Cabinet.Waybill_No COLLATE Latin1_General_CI_AS;
Go
As you can see I'm attempting to update four fields in database -waybill table- cabinet and then in return update two fields in database- test table -tblhis_waybill.
When I run this code, I get the following error:
Msg 156, Level 15, State 1, Procedure usp_wb_ptupd, Line 11
Incorrect syntax near the keyword 'FROM'.
Msg 102, Level 15, State 1, Line 9
Incorrect syntax near ' '.
Please assist
September 21, 2007 at 2:28 am
Remove comma before 2st "FROM" and remove all semicolons (";") from the script.
_____________
Code for TallyGenerator
September 21, 2007 at 8:47 am
Thanks for the reply. I made the changes and execute the procedure again. I t runs without any error messages, but it doesn't make any changes. I recently changed from sql 2000 to sql 2005. I executed one of my the previous update queries and it did not update either. I know I will figure this out eventually, but maybe somebody can point out what I'm missing. Much appreciated.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply