kdwood
asked on
Update SQL table from FoxPro OpenQuery SubSelect
Greetings Experts,
I have a project where I have SQL server tables and Visual Fox Pro tables. I need to update an inventory qty in the SQL table, based on the qty located in a Visual Fox Pro table. I created a linked server, but need help with syntax. I am stuck on matching the SKU in the SQL table to the where condition in the openquery subselect. Here is what I have, that is not working:
UPDATE [MMan].[dbo].[items]
SET qty = (select * from openquery(VFPRO, 'Select saq_qty from scsaqty where saq_sku = [MMan].[dbo].[items].itemn
GO
I get the following error:
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "Select saq_qty from scsaqty where saq_sku = [MMan].[dbo].[items].itemn
Your help would be greatly appreciated.
Best Regards,
Keith
I have a project where I have SQL server tables and Visual Fox Pro tables. I need to update an inventory qty in the SQL table, based on the qty located in a Visual Fox Pro table. I created a linked server, but need help with syntax. I am stuck on matching the SKU in the SQL table to the where condition in the openquery subselect. Here is what I have, that is not working:
UPDATE [MMan].[dbo].[items]
SET qty = (select * from openquery(VFPRO, 'Select saq_qty from scsaqty where saq_sku = [MMan].[dbo].[items].itemn
GO
I get the following error:
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "Select saq_qty from scsaqty where saq_sku = [MMan].[dbo].[items].itemn
Your help would be greatly appreciated.
Best Regards,
Keith
The command 'Select saq_qty from scsaqty where saq_sku = [MMan].[dbo].[items].itemn
I don't know exact syntax of linked server tables usage but I would guess you may use following (suppose saq_sku is of the same data type as items.itemno):
UPDATE [MMan].[dbo].[items]
SET qty = fox.saq_qty
FROM [MMan].[dbo].[items] i
INNER JOIN (select * from openquery(VFPRO, 'Select saq_qty, saq_sku from scsaqty')) fox ON fox.saq_sku = i.itemno
Another possibility is to update [MMan].[dbo].[items] directly from FoxPro by e.g. SQLEXEC() function call.
I don't know exact syntax of linked server tables usage but I would guess you may use following (suppose saq_sku is of the same data type as items.itemno):
UPDATE [MMan].[dbo].[items]
SET qty = fox.saq_qty
FROM [MMan].[dbo].[items] i
INNER JOIN (select * from openquery(VFPRO, 'Select saq_qty, saq_sku from scsaqty')) fox ON fox.saq_sku = i.itemno
Another possibility is to update [MMan].[dbo].[items] directly from FoxPro by e.g. SQLEXEC() function call.
ASKER
Thanks for the reply rrjegan,
I tried your query and at first I didn't think it would work because the query window in SQL Server Management Studio, was putting red underlines under the following:
t2.saq_QTY and t2.saq_sku. It was indicating that they were invalid column names.
However, I executed the query and it ran successfully and updated 214 rows.
Are the red underlines just a quirk because we are using the openquery method?
I tried your query and at first I didn't think it would work because the query window in SQL Server Management Studio, was putting red underlines under the following:
t2.saq_QTY and t2.saq_sku. It was indicating that they were invalid column names.
However, I executed the query and it ran successfully and updated 214 rows.
Are the red underlines just a quirk because we are using the openquery method?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you for the quick response. The solution works great. Best regards.
UPDATE [MMan].[dbo].[items]
SET qty = t2.saq_qty
FROM [MMan].[dbo].[items] t1, (select * from openquery(VFPRO, 'Select saq_sku, saq_qty from scsaqty')) t2
where t1.itemno = t2.saq_sku