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
Solved

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

Posted on 2014-02-17
9
255 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

860 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