Home > you can't specify target table for update in from clause

You can't specify target table for update in from clause

Alton Alexander
By Alton AlexanderUpdated on June 4th, 2022

The error code "Can't specify target table for update in FROM clause" happens when someone tries to UPDATE a table using a JOIN in the FROM clause. In order to UPDATE a table using a JOIN, the UPDATE statement must have a subquery.

Other common searches for this error include:

  • mysql error code 1093
  • you can't specify target table for update in from clause

1. Qualify the table name with the database and owner name

  1. To qualify the table name, use the database name and owner name. For example, if the database is named "AdventureWorks2012" and the owner is "AdventureWorks2012\dbo", the table name to update would be "AdventureWorks2012_Sales".
  2. Use the proper SQL syntax to specify the target table. The FROM clause of the UPDATE statement must specify the table name to be updated. For example, the following statement updates the "Sales" table in the AdventureWorks2012 database: UPDATE Sales
  3. Use the proper keyword(s) to identify the table to be updated. For the "Sales" table, the keyword to use is "Sales".

2. Use a four-part name for the table

The following steps can be used to fix the "you can't specify target table for update in from clause" issue:

  1. Open the table that needs to be updated in the database editor.
  2. In the table's context menu, select "Table Properties."
  3. In the "Table Properties" window, select the "Table Options" tab.
  4. In the "Table Options" tab, under the "Update" heading, click the "From" button.
  5. In the "From" dialog box, specify the name of the table to be updated.
  6. Click the "OK" button.

3. Use a synonym for the table

There are a few steps you can take to find a synonym for the table in order to fix the problem you're experiencing.

  1. Check the documentation to see if there is a recommended synonym for the table you're looking for.
  2. If there isn't a recommended synonym, search for a term that is similar to the table you're looking for.
  3. If there isn't a similar term, try using a term that is synonymous with the table.
  4. If that still doesn't work, try using a term that is an alternate spelling of the table.

Still not working? maybe try one of these options:

  1. Use a view that references the table
  2. Change the order of the FROM clause