Changing dates on a SQL table field for multiple records by using an external reference
I have around 1000 records that all have one date. I have a spreadsheet with several references such as names and postcodes which can be used to compare. The same spreadsheet has various dates that i would like to upload onto these records to replace the fixed dates.
Is there a way to upload these dates and using the names and postcodes as a reference point for where these dates should go. I'm not sure if I am making myself clear with what we need but below is an example:
The above is the picture of the query I am running. The valid_from date is what I want to change based on the data on a CSV file. I can use the first name and last names as the reference point for comparison if i copy the csv data in the sql code.
Can anyone help?
Is there a way to upload these dates and using the names and postcodes as a reference point for where these dates should go. I'm not sure if I am making myself clear with what we need but below is an example:
The above is the picture of the query I am running. The valid_from date is what I want to change based on the data on a CSV file. I can use the first name and last names as the reference point for comparison if i copy the csv data in the sql code.
Can anyone help?
ASKER
Do you mean import it into a new SQL table? Sorry I'm a bit of a novice with SQL.
Hi,
Yes import the excel into a new table, then its a lot easier to write the SQL to update the original from your new table.
HTH
David
Yes import the excel into a new table, then its a lot easier to write the SQL to update the original from your new table.
HTH
David
ASKER
Ok I have got the dates I want ready in the database. How do I change it for those entries?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
understood. Thanks David I'll test and then post the results!
ASKER
ok i am now trying to use the status date as the date for the valid_from date:
when I run this i get the following error which suggests it does not like using that field. Can you help?
Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "c.status_date" could not be bound.
update contact_categories
set valid_from = c.status_date
select c.contact_number,c.label_name, CC.activity, CC.activity_value, CC.valid_from, C.status_date, C.source_date, CC.notes
from contacts C
inner join contact_categories CC on CC.contact_number = C.contact_number
WHERE CC.activity = 'stud';when I run this i get the following error which suggests it does not like using that field. Can you help?
Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "c.status_date" could not be bound.
ASKER
please ignore. My stupidness forgot to comment out the select statement. Success!
ASKER
Perfect!
I'd do the following:
Import the spreadsheet into SQL - either directly, or use the concatenate function in Excel to create an insert statement per row.
Do this into a temp or work table
Now the data is in SQL, you can use a SQL query to update the value as needed.
HTH
David