9/13/2025 5:33:25 AM
|
|
slxdeveloper.com Community Forums |
|
|
|
The Forums on slxdeveloper.com are now retired. The forum archive will remain available for the time being. Thank you for your participation on slxdeveloper.com!
Forum to discuss general T-SQL questions and help with queries related to SalesLogix data. View the code of conduct for posting guidelines.
|
|
|
|
SQL 92 and Update Joins.
Posted: 28 Mar 06 12:21 PM
|
Can I use a SQL 92 Join like :-
UPDATE t1 SET t1.C_phno = t2.phno FROM C_Contact t1 JOIN C_Contact_EXT t2 ON t1.Contactid = t2.Contactid WHERE t1.lastname is null and t1.phno is not null
via the OLEdb provider (SLX version 6.1) . Is this supported by the SLX Oledb adapter and will it run the remote SQL Databases.
Thanks. |
|
|
|
Re: SQL 92 and Update Joins.
Posted: 28 Mar 06 3:26 PM
|
fiogf49gjkf0d I'm not 100% sure on this, but IIRC "update from" statements yield sql parse errors via the SLX provider in 6.1. I think that might have been fixed in 6.2 (or maybe not til 6.2.1).
Best bet is to give it a whirl and see what happens |
|
|
|
Re: SQL 92 and Update Joins.
Posted: 29 Mar 06 12:20 AM
|
Generally, the provider likes you to clearly identify the exact record by key that want to update, rather than some group identified by a general "where" statement.
So, you need to convert your update statement into a whole series of update statements, one for each c_contact record that needs to be updated.
To so this, change the initial update statement into a select that creates the update statements that you need, and pass those statements to the provider as you retrieve them (one by one).
So, what you need to do is pass the results of this select statement back to the provider: select 'UPDATE C_Contact SET C_phno = ''' + t2.phno + ''' where Contactid = ''' + t2.Contactid + '''' FROM C_Contact t1 inner JOIN C_Contact_EXT t2 ON t1.Contactid = t2.Contactid WHERE t1.lastname is null and t1.phno is not null
Hope this helps, Peter H
|
|
|
|
Re: SQL 92 and Update Joins.
Posted: 29 Jun 06 2:37 PM
|
fiogf49gjkf0d Here is the format that SLX will accept:
update c_contact set c_phno = (select x.phno from c_contact_ext x where c_contact.contactid = x.contactid) where lastname is null and phno is not null
Norm |
|
|
|
You can
subscribe to receive a daily forum digest in your
user profile. View the site code
of conduct for posting guidelines.
Forum RSS Feed - Subscribe to the forum RSS feed to keep on top of the latest forum activity!
|
|
|
|
|
|
|
|