2

I'm trying to update a table:

UPDATE Table_name
SET start_date = '12/12/2015' where employee_number = 111111

but I need to modify hundreds of start_dates based on employee_number.

UPDATE Table_name
SET start_date = '12/12/2015' where employee_number = 111111  
SET start_date = '01/05/2015' where employee_number = 111112 
etc....

Does sql have a preferred method of updating tables for simple conditions like mine?

C0ppert0p
  • 634
  • 2
  • 7
  • 23
  • Not sure what you are asking here. But you should use ANSI date format of YYYYMMDD. The string literals you have can cause issues based on language settings. – Sean Lange Jan 26 '18 at 19:28
  • Where is the list of changes? Is it returned by a view or stored in a table somewhere, or is it outside of the database? – Greg Viers Jan 26 '18 at 19:31
  • 1
    the changes are outside the data base. They are in a spreadsheet: Start_Date Employee_Number 11/12/2015 111111 01/05/2015 112121 02/12/2017 987988 06/23/2016 889789 .... .... 01/12/2018 767676 – C0ppert0p Jan 26 '18 at 19:37
  • If you don't want write every single update, first save the spreadsheet to the db as a table and then use my answer. – Juan Carlos Oropeza Jan 26 '18 at 19:38

2 Answers2

2

I'm going to guess you have those dates in a separated table (or file). If you don't, then just create it.

Then just join both table

 UPDATE t
 SET t.start_date  = s.start_date 
 FROM Table_name t
 JOIN sourceTable s
  ON t.employee_number = s.employee_number
Juan Carlos Oropeza
  • 47,252
  • 12
  • 78
  • 118
0

new answer

I just saw your comment that the data being from a spreadsheet. If this is a one time change, then you can load the spreadsheet into a table in SSMS and update directly from there

update table_name
    set table_name.start_date = excel_table.start_date
from table_name
join excel_table
    on excel_table.employee_id = table_name.employee_ie

Original Answer

You have a kind of ambiguous question. Does each user get it's own special date? Can you determine the users you need to update programatically?

If so, you can do something like this:

update table_name
set start_date = date_table.date
from table_name
join user_table
    on user_table.employee_number = table_name.employee_number 
join date_table
    on -- <date table join>
where
    -- filter users and dates here
ryati
  • 360
  • 3
  • 13