Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 220
  • Last Modified:

SQL Updating Records in table

Hi Guys,

I have a table of data in SQL Server 2008 where I need to go through each record and update one of the fields with the result of a sub query.

I have attached a sample of the table in the code section. I need to Update the Stop field based on the getting the actual date time from another table passing the AgentID and Date based on the current record that is processing.

Whats the best / simpliest way to achieve this?

Look for to your help.

Regards,

Nigel
Sample of data to update

AgentID	Start	                Stop	                Dur.
360	2011-02-09 16:18:50.797	2011-02-09 16:18:50.797	2
168	2011-02-10 08:28:37.133	2011-02-10 08:28:37.133	2
16	2011-02-10 08:31:38.047	2011-02-10 08:31:38.047	2

Open in new window

0
Nigel_Taylor
Asked:
Nigel_Taylor
  • 3
  • 3
1 Solution
 
mayank_joshiCommented:
this example may be helpful:-

update t
   set last_name = s.last_name
     , first_name = s.first_name
  from tbl_employees t
  join stage_employees s
    on t.ssn = s.ssn

Open in new window

0
 
mayank_joshiCommented:
in your case it may be like:-

update table1 set stop=table2.stop from table1 join  table2 on table1.AgentID=table2.AgentID
and table1.Start =table2.Start

Open in new window


you can write the same query like this also:-

update table1 set stop=table2.stop from  table2 where table1.AgentID=table2.AgentID
and table1.Start =table2.Start

Open in new window



0
 
Nigel_TaylorAuthor Commented:
Thanks for the quick response Mayank. I am playing with it now and will let you know how I get on.

Nigel
0
Independent Software Vendors: 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!

 
Nigel_TaylorAuthor Commented:
Okay I have the below

UPDATE #AgentsNoLogon SET [Stop] = AT.[Stop] FROM AgentTracking AT
	WHERE (#AgentsNoLogon.AgentID = AT.AgentID AND 
			CONVERT(datetime, CONVERT(varchar(10), #AgentsNoLogon.[STOP], 120), 120) = CONVERT(datetime, CONVERT(varchar(10), AT.[Stop], 120), 120)
		   )

Open in new window


How do I get it so the Stop record entered is from the following query

SELECT TOP(1) [Stop]
		FROM AgentTracking
		WHERE [AgentId] = [AgentID] AND 
			CONVERT(datetime, CONVERT(varchar(10), [STOP], 120), 120) = CONVERT(datetime, CONVERT(varchar(10), [Stop], 120), 120)
		ORDER BY [Stop] DESC

Open in new window


Hope that makes sense.

Regards,

Nigel
0
 
mayank_joshiCommented:
do you need this:-

UPDATE #AgentsNoLogon  SET [Stop] = 
(SELECT TOP(1) AT.[Stop]
                FROM AgentTracking AT
                WHERE AT. [AgentId] =#AgentsNoLogon. [AgentID] AND 
                        CONVERT(datetime, CONVERT(varchar(10),AT. [STOP], 120), 120) = CONVERT(datetime, CONVERT(varchar(10),#AgentsNoLogon. [Stop], 120), 120)
                ORDER BY AT. [Stop] DESC)

Open in new window

0
 
Nigel_TaylorAuthor Commented:
Perfect, thanks for your help on this.

Regards,

Nigel
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now