October 19, 2009 at 1:39 pm
I have a weekly process that reads the excel file puts into the temp table. Then I use SSIS to load directly into AS400 table. This works fine without problem.
Now the issue is I need to only load the new records in AS400 and UPDATE if the record already exists. I am having trouble to make UPDATE stament work
Here is the update stament in Execute SQL Task
UPDATE a
SET XUBCUNO = '72065795'
FROM IC1480BFPD.XROHUB a
WHERE XUBCUNO = '7206579'
Here is the error:
" failed with the following error: "Keyword not expected. Valid tokens: . SQLSTATE: 42601, SQLCODE: -199". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Any help would be appreciated. BTW, connection works. Inserting fine. Also DELETE works as well.
October 20, 2009 at 1:12 pm
Have you tried ...
UPDATE IC1480BFPD.XROHUB
SET XUBCUNO = '72065795'
WHERE XUBCUNO = '7206579'
October 20, 2009 at 4:19 pm
Yes. That worked. Now my questions are
1) Seems like it has problem with the Key word FROM. As soon as I put in FROM, it fails. This will create me a problem when I have to bring more than 1 table in the UPDATE.
FOR e.g,
UPDATE IC1480BFPD.XROHUB
SET IC1480BFPD.XROHUB = T2.HUB,
IC1480BFPD.XROHUB1 = T2.HUB1
FROM IC1480BFPD.XROHUB T1
JOIN Datawarehouse480.dbo.Table2 T2 ON T1.ID = T2.ID
2) Also, the same thing holds true for LEFT JOIN. ( I need to check if the record already exists before inserting in).
SELECT *
FROM Datawarehouse480.dbo.Table1 T1
LEFT JOIN IC1480BFPD.XROHUB T2 ON T1.ID = T2.ID
WHERE T2.ID IS NULL
Any help on this would be appreicated.
October 20, 2009 at 4:25 pm
What version of SQL Server are you using?
October 20, 2009 at 5:17 pm
SQL server 2005
October 21, 2009 at 1:28 am
I suspect that you need to use SQL syntax which is supported on DB2.
Check here and search for INSERT as a possible resource to get you through this.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 21, 2009 at 7:19 am
Phil makes a good point. There are in fact some slight syntax differences between SQL Server and DB2. If you ever decide to make the jump to SQL 2008, it has a new T-SQL command called MERGE that will handle inserts, deletes, and updates automatically with one statement.
October 21, 2009 at 12:38 pm
barunpathak (10/20/2009)
Yes. That worked. Now my questions are1) Seems like it has problem with the Key word FROM. As soon as I put in FROM, it fails. This will create me a problem when I have to bring more than 1 table in the UPDATE.
FOR e.g,
UPDATE IC1480BFPD.XROHUB
SET IC1480BFPD.XROHUB = T2.HUB,
IC1480BFPD.XROHUB1 = T2.HUB1
FROM IC1480BFPD.XROHUB T1
JOIN Datawarehouse480.dbo.Table2 T2 ON T1.ID = T2.ID
2) Also, the same thing holds true for LEFT JOIN. ( I need to check if the record already exists before inserting in).
SELECT *
FROM Datawarehouse480.dbo.Table1 T1
LEFT JOIN IC1480BFPD.XROHUB T2 ON T1.ID = T2.ID
WHERE T2.ID IS NULL
Any help on this would be appreicated.
i'm not 100% sure, but i thought i read somewhere that using FROM in an update statement is not ANSI compliant and is a MSSQL specific feature
October 21, 2009 at 12:43 pm
That is correct SSC. SQL syntax is a little different on some commands between SQL server and IBM DB2.
October 21, 2009 at 1:59 pm
Thanks guys. I am almost there. I added a linked server to AS400 and can easily bring the AS400 table in the join and find the record that do not exist in AS400 table.
I am almost close to the second problem (regarding UPDATE statement).
I looked at the sample DB2 SQL and still having issues:
My statement is like this: (Seems like you need to provide WHERE EXISTS for update to work)
UPDATE ASWTEST.S1012A5C.IC1480BFPD.XROHUB T2
SET T2.XUBRNAM = (SELECT XUBRNAM FROM [DataWarehouse480].[dbo].[Temp_SalesRep] T1 WHERE T1.XUBCUNO = T2.XUBCUNO)
WHERE EXISTS (SELECT 1 FROM [DataWarehouse480].[dbo].[Temp_SalesRep] T1 WHERE T1.XUBCUNO = T2.XUBCUNO)
Error I am getting is as follows:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'T2'.
Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'WHERE'.
Any help would be greatly appreicated.
October 21, 2009 at 2:05 pm
Try removing the T2 aliasing and writing out the full name of the table. You cannot alias a table in the first line of an update statement, even in T-SQL (I think).
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 21, 2009 at 2:27 pm
Sorry. The error I posted earlier might not be relevant. That was run on query analyzer.
Here is the exact error message when I tried to run the queriy in SSIS on AS400 connection.
"Token SET was not valid. Valid tokens: . SQLSTATE: 42601, SQLCODE: -104". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
October 22, 2009 at 6:33 am
UPDATE ASWTEST.S1012A5C.IC1480BFPD.XROHUB T2
SET T2.XUBRNAM =
(SELECT XUBRNAM
FROM [DataWarehouse480].[dbo].[Temp_SalesRep] T1
WHERE T1.XUBCUNO = T2.XUBCUNO
AND EXISTS
(SELECT 1 FROM [DataWarehouse480].[dbo].[Temp_SalesRep] T1 WHERE T1.XUBCUNO = T2.XUBCUNO))
See if using AND instead of WHERE and moving the closing paren works.
Greg E
October 22, 2009 at 7:39 am
No. Still the same error persists.
Token SET was not valid. Valid tokens: . SQLSTATE: 42601, SQLCODE: -104". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
October 22, 2009 at 7:58 am
Phil Parkin (10/21/2009)
Try removing the T2 aliasing and writing out the full name of the table. You cannot alias a table in the first line of an update statement, even in T-SQL (I think).
Did you follow this suggestion? aliasing in update clause is not allowed
---------------------------------------------------------------------------------
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply