Check for record syntax + Multi Table join

[fergy1]

Hello-

How could I use a query such as the following but check to see if the record exists before running it? Such as Cal_User_ID = tblPersonnelID is Null or?

Update Cal_User SET Cal_User.Cal_User_UserName = tblPersonnel.Username and Cal_User.Cal_User_Password = tblPersonnel.Password FROM tblPersonnel

 

[ESquared]

I'm not sure what update you want to perform.

If you have one table with usernames and passwords, and wish to update another table with those same usernames and passwords, there has to be a key which relates the two tables.

Pseudo-code would be something like this:

Set each username and password in Cal_User...
to some value from tblPersonnel...
which can be correlated by UserID in both tables.

I need to know how the third line works, because "some value in tblPersonnel" isn't enough to get the job done.

Perhaps, though, you just want to update the password where the usernames are the same. That would be something like:

Set each password in Cal_User...
to some value from tblPersonnel...
which can be correlated by username in both tables.

Once you reply with what it is you want, we can help you with a query to perform it.

-------------------------------------
It is better to have honor than a good reputation.
(Reputation is what other people think about you. Honor is what you know about yourself.)

 

[fergy1]

Actually the query I posted works great for doing an update. But an update is not what I want, I need to query to check to see if the user Id exists in the other table and if not then perform an insert rather than an update.

Thanks
James F.

 

[ESquared]

I'm surprised your query works fine for the update. Have you checked to see that the results are actually what you want? From what I can see, using your query, all the records in your Cal_User table will end up with the same password and username. The password and username they get set to will depend on things like which field has a clustered index, but it is effectively random.

Before I can help you with any insert, I have to help you get the update right...

-------------------------------------
It is better to have honor than a good reputation.
(Reputation is what other people think about you. Honor is what you know about yourself.)

 

[SQLSister]

Fergy you don't need to check if it exists you simply need to do the insert using the selcet command rather than the values clause. First you do the update to update any existing records, then you do the insert to add any new ones.

SAmple code:

UPDATE table1
SET field1 = table2.field1,
field2 = table2.field2,
field3 = table2.field3
FROM table1
INNER JOIN table2
ON table1.id = table2.id

INSERT INTO table1 (field1, field2, field3)
SELECT field1, field2, field3 FROM table2 LEFT JOIN table1
ON table1.id = table2.id
WHERE table1.id is null

Questions about posting. See faq183-874

 

[ESquared]

Exactly, SQLSister! I think the problem will be application, though.

-------------------------------------
It is better to have honor than a good reputation.
(Reputation is what other people think about you. Honor is what you know about yourself.)