Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
fmardani
Constraint Violating Yak Guru
433 Posts |
 Posted - 2005-07-06 : 11:11:38
|
| Hi,I have written the following select query so that the system knows when to insert or update.What I am basically doing is to check if the record already exists, if not then insert else do an update...The table holds the people details...Let's say you would like to insert the person 'mike judge' i.e. firstname = 'mike', lastname = 'judge'. This person may not have an id i.e. PhoneBookFeedID.And another person with the same name may already exist in the table. So although the name of these two people is the same, they will have a different (phone1, title, email) since they are two different people but with the same name (Remember there is no ID)My current sql works well except if there are two people with the same name and lastname and are in the same company, but I need to distiguish them using the fields --> phone1, title, emailIm the sql below I have commented three of the fields which I think should be used to complete the query.I think I have to add OR somewhereHow is this done please?Thanksif not exists( select PhoneBookID from tblPhoneBook where ( CompanyID = @CompanyID AND isnull(PhoneBookIDfeed, 0) = isnull(@PhoneBookIDfeed,0) AND ltrim(rtrim(Firstname)) = ltrim(rtrim(@firstnameFeed)) AND ltrim(rtrim(Lastname)) = ltrim(rtrim(@LastnameFeed)) --AND isnull(Title,0) = isnull(@TitleFeed, 0) --AND isnull(Phone1,0) = isnull(@Phone1Feed, 0) --AND isnull(Email,0) = isnull(@EmailFeed, 0) ) ) begin insert... endelse begin update... end |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2005-07-06 : 11:57:22
|
| What's wrong with your query? Should work if you just uncomment the fields - you will also have to add these to the join in the update.Think about somethig like this - it's simpler.update tblPhoneBooksetwhere CompanyID = @CompanyIDAND isnull(PhoneBookIDfeed, 0) = isnull(@PhoneBookIDfeed,0) AND ltrim(rtrim(Firstname)) = ltrim(rtrim(@firstnameFeed))AND ltrim(rtrim(Lastname)) = ltrim(rtrim(@LastnameFeed))AND isnull(Title,0) = isnull(@TitleFeed, 0)AND isnull(Phone1,0) = isnull(@Phone1Feed, 0)AND isnull(Email,0) = isnull(@EmailFeed, 0) ) ) if @@rowcount = 0 insert tblPhoneBook==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
fmardani
Constraint Violating Yak Guru
433 Posts |
Posted - 2005-07-06 : 12:02:38
|
| At present my sql does not fully work if let's say there are two people with the same name and one of them has email address.Thanks |
|
|
|
|
|
|
|
|
if exists