if exists update else insert 1

[puter55]

I am trying to do an update if the record exists else insert the record for multiple records. The way I normally handle this is with a left join/where is null statement at the end of my insert statement. I'm not sure how to handle the subquery in my statement using this method.

Update v1
    set ...
FROM vendor v1
JOIN vend v2
ON v2.num = v1.ven_number
and v2.uniq_id in
                  (select vend_id from inv a
                  inner join vend v2 
                  on vend_id = v2.uniq_id
                  where post_dte > dateadd(dd, -365,getdate())
                  union
                  select inv_vendID from invoice i
                  inner join batch b
                  on b.bat_batID = i.inv_batid
                  inner join vend 
                  on i.inv_vendid = v2.uniq_id
                  where b.bat_post_date > dateadd(dd,-365,getdate())
				)
insert into vendor 
	....
select 
		.....
from vend v2
left outer join vendor v1
on v2.num = v1.ven_number 
Where v1.ven_number is null
   .... (how do I handle the subquery here?)

 

[RiverGuy]

You can select from a Derived Table

insert into vendor
    ....
select
        .....
from 
   (SELECT * FROM vend
    WHERE uniq_id in
        (select vend_id from inv a
                  inner join vend v2
                  on vend_id = v2.uniq_id
                  where post_dte > dateadd(dd, -365,getdate())
                  union
                  select inv_vendID from invoice i
                  inner join batch b
                  on b.bat_batID = i.inv_batid
                  inner join vend
                  on i.inv_vendid = v2.uniq_id
                  where b.bat_post_date > dateadd(dd,-365,getdate())
                )

 ) v2
left outer join vendor v1
on v2.num = v1.ven_number
Where v1.ven_number is null
   .... (how do I handle the subquery here?)

 

[puter55]

Brilliant