?
Solved

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

Posted on 2014-02-17
9
Medium Priority
?
265 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

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

Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

752 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