I am trying to update SIC Codes on the Account table with data provided by an outside source on another table. I tried executing the following SQL statement in the SLX Administrator, but I get errors: "Failed to parse SQL"

UPDATE ACCOUNT
SET MODIFYDATE=GETDATE(),
 MODIFYUSER='Admin',
 SICCODE = L.SICCODE 
from ACCOUNT A, LoadTable L
where A.ACCOUNTID = L.ACCOUNTID 

This query works fine in SQL Server Query Analyzer, but I need the changes to be synched down to
the remote databases, so I need to run them in the SLX Admin. I am on SLX ver 7.5.2.  Any suggestions?

1. The SLX OLE DB Provider automagically takes care of ModifyData, ModifyUser so need for Setting those fields.

2. SLX OLE DB Provider doesn't like none explicit join updates....fully qualifed selects work just fine.

Try this

UPDATE ACCOUNT
SET  SICCODE =
(SELECT L.SICCODE 
from LoadTable L
where ACCOUNT.ACCOUNTID = L.ACCOUNTID  )

Thanks RJ, but I just tried it and I got the "Failed to Parse SQL" error. Any other ideas?

drop the modifydate out of it.

the field is managed by the provider and the getdate() function can't pass through sync so will fail.

Test you sql through QueryAnalyzer.exe using a slx provider connection (but be advised if it works, it will be in the sync queue as well, based on the setup)

Quote: Originally posted by Lane Test you sql through QueryAnalyzer.exe using a slx provider connection

Is that possible in QA? Been a while since I used it.

A (perhaps safer) way of testing such statements is using the 'Test' function in the Tools / Execute SQL part of Administrator, which executes the statement as part of a transaction and then rolls it back (after verifying whether it worked).

I second using the Execute SQL function in Administrator.  It is a much safer way of testing SQL scripts.

I used the 'TEST' function in Tools/Execute SQL, and I didn't get the 'Fail to Parse Sql' error, but it also tried to update every account on the account table.  It does not update ONLY the accounts where the accountid matches the accountid on the load table, but ALL accounts, so this will not work.  

This is the SQL I tested:

 UPDATE ACCOUNT
SET  SICCODE =
(SELECT I.SICCODE 
from INFOUSA_LOAD I
where ACCOUNT.ACCOUNTID = I.ACCOUNTID  )

That update will update all acounts because there is no where statement for the UPDATE statement.  You need to move the where to outside the subquery.  In addition to that I think you need to refine the sub-query to include a different where statement condition unless there is a SLX accountid field in the INFOUSA_LOAD table.  The basic SQL will look like this

UPDATE ACCOUNT
SET  SICCODE =
(SELECT I.SICCODE 
from INFOUSA_LOAD I WHERE {condition} = {condition})
WHERE ACCOUNT.ACCOUNTID = I.ACCOUNTID

Be aware that the sub-query can cause a failure if it returns more than one record.  Verify the where conditions for the sub-query so it will only return one valid record.  There is a way to gaurentee only one record is return but I wouldn't advise using this method because if more than record is returns indicates there is a data validity issue.

Quote: Originally posted by Bailey1 I used the 'TEST' function in Tools/Execute SQL, and I didn't get the 'Fail to Parse Sql' error, but it also tried to update every account on the account table.  It does not update ONLY the accounts where the accountid matches the accountid on the load table, but ALL accounts, so this will not work.   This is the SQL I tested:  UPDATE ACCOUNT SET  SICCODE = (SELECT I.SICCODE  from INFOUSA_LOAD I where ACCOUNT.ACCOUNTID = I.ACCOUNTID  )

That's because you have no WHERE clause in your outer SQL. Try this (untested):

UPDATE ACCOUNT
SET  SICCODE = 
(SELECT I.SICCODE 
from INFOUSA_LOAD I
where ACCOUNT.ACCOUNTID = I.ACCOUNTID  )
WHERE ACCOUNT.ACCOUNTID in (Select i2.AccountID from INFOUSA_LOAD I2)

Thanks Phil - I think I got it to work!