Solved

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

Posted on 2014-02-17
9
238 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
 

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Creating and Managing Databases with phpMyAdmin in cPanel.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

760 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

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now