January 16, 2009 at 2:53 pm
Here's the scenario:
Say we have two tables (tempTable and leaseInfo); both tables have leaseNum and leaseOwner fields with the same data types. The tempTable has values populated in the leaseNum and leaseOwner fields like this:
leaseNum = 123
leaseOwner = 'Mark'
I want to transfer this leaseOwner value from the tempTable to a different leaseNum (456) in the leaseInfo table. This following update code below handles this update fine as long as leaseNum 456 exists in the leaseInfo table.
declare @LEASENUM_NEW as numeric(3)
declare @LEASENUM_OLD as numeric(3)
set @LEASENUM_NEW = 456
set @LEASENUM_OLD = 123
update leaseInfo
set [leaseOwner] =
(select [leaseOwner]
from tempTable
where leaseNum = @LEASENUM_OLD)
where leaseNum = @LEASENUM_NEW
But, in the case where leaseNum 456 does not exist, I would like to do a insert/into statement instead. What I am looking for here is some kind of conditional (similar to MySQL's IFNULL) that would either perform the update/set if leaseNum 456 exists or an insert/into if it does not.
Here the leaseInfo table does NOT have a leaseNum that matches the value 456 in the leaseInfo table, so this select statement below returns no results:
select * from leaseInfo where leaseNum = 456
Any suggestions on how I can accomplish this would be greatly appreciated.
January 16, 2009 at 3:28 pm
You're looking for an upsert. Here's a good thread on the topic. I'm in favor of the UPDATE....if @@rowcount = 0 INSERT method.
January 19, 2009 at 12:32 pm
Thanks for pointing me in the right direction - the UPSERT worked. However, I am struggling with one thing pertaining to the update part.
Basically, I did something like this...
if exists
(select statement)
update table
set field = something
where condition matches
else
insert into table values (whatever values)
I cannot find a way to perform multiple updates under the IF part of the UPSERT. I can only get it to perform one update, then it generates an error code.
Suggestions?
January 19, 2009 at 12:43 pm
I could be mis-interpreting what you are saying here (forgive me if so) but two points of interest relating to your last post:-
1. An update statement will only update one table at a time in a single statement
2. If you want to run multiple statements after an IF use a BEGIN END block, as follows:-
IF EXISTS (SELECT 1 FROM xxxx WHERE yyy = zzz)
BEGIN
UPDATE Table1
SET field = value
WHERE xxx = yyy
UPDATE Table2
SET field = value
WHERE xxx = yyy
END
ELSE
.......
Hope that helps..
Si
January 19, 2009 at 12:49 pm
You interpreted it correctly. I was hoping it would be something simple. I'll try using BEGIN and END statements. I am going to use it to update multiple fields (columns) in the same row of the same table.
Thanks for the lightning fast response!
January 19, 2009 at 1:01 pm
I'm thinking you want the EXISTS to be part of your INSERT statement, and not AROUND it. That way you can do all of the inserts in one shot and you don't need to loop through anything.
What you seem to be setting up will involve multiple loops or cursors, which are unnecessary so far given what you've told us of your need.
You should be looking at something like
INSERT into TableA (mycolumns)
select mycolumns
from TableB tb
where not exists (select null from tableA ta where ta.id=tb.id)
update TableA
set whatever
where conditionmatch
Note that by doing the insert first you don't need anything fancy on your update, since you KNOW that there will be a match.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 19, 2009 at 3:07 pm
Matt Miller (1/19/2009)
You should be looking at something like
INSERT into TableA (mycolumns)
select mycolumns
from TableB tb
where not exists (select null from tableA ta where ta.id=tb.id)
update TableA
set whatever
where conditionmatch
Note that by doing the insert first you don't need anything fancy on your update, since you KNOW that there will be a match.
Everything is right but the order of the statements.
UPDATE must be first , then you won't update the rows you just inserted.
:hehe:
And I'd write UPDATE like this:
UPDATE ta
SET whatever = tb.whatever
from TableB tb
inner join tableA ta ON ta.id=tb.id
WHERE ta.whatever <> tb.whatever
WHERE clause helps to minimize LOG file.
😉
_____________
Code for TallyGenerator
January 20, 2009 at 6:38 am
Agreed. On small sets - it's sometimes faster to just do the insert first, but by far and large - it's better not to do the update on the very rows you inserted.
Still - you have to be careful with your WHERE clause, because you forgot about possible NULLS. ("forgot" in the example - I'm sure you wouldn't forget it in your own code.)
Otherwise, again - agreed.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply