CODEWITHSUNDEEP
sqlms-access
user3462117
user3462117

Reputation: 11

Query inside another query

i am trying to perform this task. Write a query to add a 2015 entry to the Salesmen table for each salesperson who worked in 2014. Their name, region, and salary should be the same as they were in 2014. Their sales should be $0. Their bonus level should be 'Low'.

Upvotes: 1

Views: 46

Answers (2)

Milan Zavišić
Milan Zavišić

Reputation: 301

Try this:

INSERT INTO salesman (firstName, lastName, region, year, salary, sales, bonusLevel)
SELECT firstName, lastName, region, 2015, salary, 0, 'low'
FROM salesman so
WHERE so.year = 2014;

Upvotes: 1

mellamokb
mellamokb

Reputation: 56779

You can do this with an INSERT...SELECT query. The idea is to INSERT into a table, the results of SELECTing from one or more tables.

Without knowing the exact schema of your tables, here is a rough idea how you could implement it.

INSERT INTO Salesmen
  (year, name, region, salary, sales, bonus)
SELECT
   2015, name, region, salary, 0    , 'Low'
FROM
  Salesmen WHERE Year=2014

This will insert data into Salesmen for the specific columns listed, using the values listed in the SELECT. Some of the values are field names, so they will be used from the result of FROM Salesmen WHERE Year=2014. The hard-coded values will be inserted as-is into their corresponding columns for each row.

Upvotes: 1

Related Questions