Solved

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

Posted on 2014-02-17
9
256 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 

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 500 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
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.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

713 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