asked
Query not valid? Trying to update a salary based on the bank the person works for
I have two tables Works(Name char(25),BankID int(4),Salary int(8)) Company(BankID int(4), CompanyName char(25), City char(25)) I'm trying to increase the salaries of people in the works table by 10% based on if they work for a specific CompanyName (i,e, maybe First bank or Chase) What I have is: UPDATE Works SET Salary = (Salary * .10) + Salary WHERE (SELECT Works.BankID FROM Works JOIN Company ON Works.BankID = Company.BankID and Company.CompanyName = "First bank") I'm getting the error ***#1093 - You can't specify target table 'Works' for update in FROM clause*** Perhaps my approach is incorrect, im a newbie at SQL and have never done anything like this before
My assumptions: 1. This is a homework question 2. You've accidentally posted a MySQL question to a SQL Server forum (and the wrong section - meta-askssc - at that). Putting these to one side, the following is an approach that works for SQL Server, so you might be able to modify it for the MySQL dialect without too much trouble. UPDATE wk SET wk.salary = (wk.salary * .10) + wk.salary FROM dbo.works wk WHERE exists(SELECT * FROM dbo.company cm WHERE cm.bank_id = wk.bank_id and cm.company_name = 'First bank' ) ;
20 People are following this question.
