x

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

more ▼

asked Oct 30, 2016 at 01:04 PM in Default

avatar image

yusha
1 1

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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'
              )
 ;
more ▼

answered Oct 30, 2016 at 08:12 PM

avatar image

GPO
4.9k 41 51 58

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x171
x110

asked: Oct 30, 2016 at 01:04 PM

Seen: 42 times

Last Updated: Oct 30, 2016 at 08:18 PM

Copyright 2016 Redgate Software. Privacy Policy