Update and function

I am a student, this is part of my homework.

I have to Update two employee’s pay by 10%…is there a function for that or do I have to calculate the figures and just change the numbers i.e.

  • Need function the gives DateTime for first day of week given the day number for first day of week
  • What is the T-SQL equivalent of MySQL syntax LIMIT x, y?
  • Storing a SHA512 Password Hash in Database
  • SQL Server CLR Aggregate: Serialize a Dictionary?
  • Update xml node attributes in SQL Server, filtering on other attribute
  • How to improve performance of relatively large table
  • update Employee
    set Wage=10
    where Wage=51000

    This is the entire question:
    choose an EEO-1 Classification:
    Increase all employees’ salaries that have:
    the selected EEO-1 classification by 10%.

    The EEO classification is in a Job Title table and the Salaries are in the Employee Table. I need to join them somehow also. It needs to be a single statement…


    5 Solutions collect form web for “Update and function”

    As this is homework you need to know the smart alec answer. The answers posted using JOIN syntax are wrong because it is proprietary code. The correct smart alec answer is to use Standard SQL (preferably one supported by your dialect of choice e.g. SQL Server). You should also point out that ISO/IEC 11179 Standard for data element names requires a plural names for set that may have more than one member (e.g. JobTitles rather than JobTitle) and to look for established collection names already used in the enterprise (e.g. Personnel rather than Employees). Furthermore, if the job title attribute is modelled using a separate table then salaries should have their own table too (and named Payroll rather than Salaries):

    UPDATE Payroll 
       SET Wage = Wage * 1.1 
                   SELECT * 
                     FROM Employees AS E1
                          INNER JOIN JobTitles AS J1
                             ON E1.employee_number = J1.employee_number
                                AND J1.EEO_classification = 'EEO-1'
                    WHERE E1.employee_number = Payroll.employee_number

    Then again, the enterprise would need to past wages as well as current wages (e.g. to be able to file tax returns), therefore Payroll is likely to be a valid-state temporal table and you would need to find the most recent state for each employee… You know, the more I think about this the more I’m convinced this enterprise doesn’t exists at all. My advice is to assume it is a trick question and refuse to answer it until they’ve at least fixed the schema… perhaps ask about an internship during the summer so you could fix it yourself…

    The secret with updates is to make sure you can do the select first.

    So focus on how you can get all the fields you want to select.

    Your key is going to be an Inner Join (no doubt someone will give a full answer but I’m against interrupting the learning process).

    Next, you need to work out how to calculate the wage.

    The fortunate thing is you can do this in your select first to make sure you’re on the right track.

    Something like:

    SELECT *, (wage*1.1) as newWage FROM Employee 

    Once you are happy that the select looks good you can turn it into an UPDATE and the cool thing is you can actually update a joined query and just grab your select inner join and where stuff.

    UPDATE Employee
    SET Salary.Wage = (wage*1.1)
    FROM Employee

    What you need is an UPDATE statement which uses an INNER JOIN.

    UPDATE Employee
    SET Wage = e.Wage * 1.10
    FROM Employee as e
    INNER JOIN JobTitle as jt
      ON jt.[id] = e.[jobId]
    Where jt.Title = 'EEO-1'

    I’ve had to make some assumptions about your schema here, I’m assuming there is some link between Employee and Job Title tables. I’m also assuming the Job Title is a string and not an integer. Hopefully this gives you the rough outline of the syntax you need.

    Something like:

    declare @classification varchar(100)
    set @classification = '<your value>'
    update Employee set Wage = Wage *1.10 
    join JobTitle on Employee.JobTitleId = JobTitle.JobTitleId
    where JobTitle.[EEO-1 Classification] = @classification

    Since it is homework, I’ll give you some hints:

    You can update the Wage by making it equal to itself + 10% or multiply by 110%

    The second table can be addressed in the where clause; hint: there needs to be a field in each table that are equivalent.

    The EEo-1 classification can be set to equal a parameter.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.