[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

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

Update table from a SELECT

I run a SELECT

now I want to update what is in that select to my table I have

how do I update from a SELECT (this is different compared to VFP)

So I then can compare the two for any new records (EXISTS?) and then Email from there
SELECT co 'Company',
	loc 'Location',
	sdat 'Date',
	ord# 'Order',
	rel# 'Release',
	user 'User'
FROM OPENQUERY (MyData, '
SELECT *
FROM TableAS400
WHERE type IN(''RI'', ''IR'')	
')

-- Update Table2
-- Compare every hour to see if new orders are written - 8am --- to 5pm

Open in new window

0
jaymz69
Asked:
jaymz69
  • 4
  • 2
2 Solutions
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
You can use the query below to UPDATE records in table2

Schedule the below query in Agent job to schedule it to run every hour between 8 am to 5 pm and send mails as required.
Kindly let me know if you need any help in setting up agent job
with cte as (
SELECT co 'Company',
	loc 'Location',
	sdat 'Date',
	ord# 'Order',
	rel# 'Release',
	user 'User'
FROM OPENQUERY (MyData, '
SELECT *
FROM TableAS400
WHERE type IN(''RI'', ''IR'')	
'))
update ur_table
set col1 = t2.col1
FROM cte t1
JOIN ur_table t2 on t1.pk = t2.pk

Open in new window

0
 
Kevin CrossChief Technology OfficerCommented:
Raja, I think you meant like this:
;with cte as (
SELECT co 'Company',
	loc 'Location',
	sdat 'Date',
	ord# 'Order',
	rel# 'Release',
	user 'User'
FROM OPENQUERY (MyData, '
SELECT *
FROM TableAS400
WHERE type IN(''RI'', ''IR'')	
'))
update t2
set t2.col1 = t1.col1 -- columns other than key
FROM cte t1
JOIN ur_table t2 on t1.pk = t2.pk -- probably 'Order'
;

Open in new window


Although, if you are looking to do an update and then also check for totally new rows, I would use a #temp table or an @table variable.

DECLARE @myData({column list with data types});
INSERT INTO @myData
SELECT co 'Company',
	loc 'Location',
	sdat 'Date',
	ord# 'Order',
	rel# 'Release',
	user 'User'
FROM OPENQUERY (MyData, '
SELECT *
FROM TableAS400
WHERE type IN(''RI'', ''IR'')	
');

update t2
set t2.col1 = t1.col1 -- columns other than key
FROM @myData t1
JOIN ur_table t2 on t1.pk = t2.pk -- probably 'Order'

-- check for new rows
if exists (
   select 1 
   from @myData t1 
   where not exists(
      select 1 
      from ur_table t2 
      where t1.pk = t2.pk
   )
) begin
-- send emails
-- insert additional rows to database
end
;

Open in new window


You can't use CTE if you need that approach since it can only be used in the immediate SELECT|INSERT|UPDATE|DELETE statement and you need at least 2-3 different calls to the data here IMHO.

Hope that helps!
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Yes, Kevin. I meant that..

I think we can do it either via cte or also via temp tables / variables as you have mentioned...
Kindly correct me if I am wrong why cte will not work.
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
Kevin CrossChief Technology OfficerCommented:
The CTE approach wouldn't work if had to have multiple SQL statements.  With SQL 2008, MERGE is an option to do the insert of new and updates at the same time; however, in this case, the new rows need to trigger send mail.  Therefore, it has to be split in two steps.  Therefore, unless you repeat the CTE definition twice (i.e., running open query more than once), then CTE won't work.
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
>> The CTE approach wouldn't work if had to have multiple SQL statements.

Yes, I got what you meant..
In the meanwhile, as per the question I thought it would be a single update and hence I used CTE.
Hope we are meeting after a long time Kevin..
0
 
jaymz69Author Commented:
Yes, I need to update the table bu everyhour it runs I only want to get the NEW RECORDS and have those in an Email.

YES I would like to see how you set up the agent becasue I tried and it crashed
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Steps to create a T-SQL based Agent job here:

http://msdn.microsoft.com/en-us/library/ms187910.aspx
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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