Improve company productivity with a Business Account.Sign Up

x
?
Solved

Changing dates on a SQL table field for multiple records by using an external reference

Posted on 2014-02-17
9
Medium Priority
?
274 Views
Last Modified: 2014-02-21
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:

Picture of query
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?
0
Comment
Question by:MSSC_support
  • 6
  • 3
9 Comments
 
LVL 35

Expert Comment

by:David Todd
ID: 39865888
Hi,

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
0
 

Author Comment

by:MSSC_support
ID: 39866760
Do you mean import it into a new SQL table? Sorry I'm a bit of a novice with SQL.
0
 
LVL 35

Expert Comment

by:David Todd
ID: 39868176
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
0
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

 

Author Comment

by:MSSC_support
ID: 39868693
Ok I have got the dates I want ready in the database. How do I change it for those entries?
0
 
LVL 35

Accepted Solution

by:
David Todd earned 2000 total points
ID: 39868741
Hi,

Here is my guess
update cc
set valid_from = dd.new_valid_from
-- select c.contact_number, cc.activity_value, c.status, cc.valid_from, dd.new_valid_from
from dbo.Contacts c
inner join dbo.Contact_Categories cc
	on cc.contact_number = c.contact_number
left outer join dbo.NewContacts dd
	on dd.contact_number = c.contact_number
where
	cc.activity = 'STUD'
;

Open in new window


Note:
I first run this by selecting from the select (just after the comment) on down. That way I get to see the likely change before making it.

If that is okay, I can then run the entire script.

HTH
  David
0
 

Author Comment

by:MSSC_support
ID: 39870322
understood. Thanks David I'll test and then post the results!
0
 

Author Comment

by:MSSC_support
ID: 39870400
ok i am now trying to use the status date as the date for the valid_from date:

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';

Open in new window


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.
0
 

Author Comment

by:MSSC_support
ID: 39870467
please ignore. My stupidness forgot to comment out the select statement. Success!
0
 

Author Closing Comment

by:MSSC_support
ID: 39877146
Perfect!
0

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Here is how to restore SQL Server database to the point in time.  Follow the step by step approach to restore your database at a specific point in time and also understand its alternate approach.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

584 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question