Update using WHERE EXISTS

[bifteki]

I want to make a more complex UPDATE query but I can't seem to make it work.

We have 3 tables, tbl_Companies, tbl_Persons and itbl_company_person.
itbl_company_person is an intermediate table, used because the Company can
have more than one person in it (obviously) and the person can sometimes be a
part of more than one companies (but mostly because when a person leaves a
company to join another one, we don't delete the intermediate record, instead
we have a fld_active_relation which becomes TRUE or FALSE accordingly).

In the itbl_company_person table, there is a fld_personal_email, which is the
personal e-mail of the person in the company. Also in the tbl_Companies table
there is a fld_company_e_mail field (the general e-mail of the company, eg
info@company.com). I just added a new field in itbl_company_person, named
fld_preferred_email. I want to fill this table, giving the value of
fld_personal_email where there is one or, if the fld_personal_email is NULL,
I want to give it the value of fld_company_e_mail.

The query I wrote is the following:

UPDATE itbl_company_person
SET itbl_company_person.fld_preferred_email= (SELECT tbl_Companies.
fld_company_e_mail
FROM tbl_Companies
WHERE itbl_company_person.fld_personal_email IS NULL)
WHERE EXISTS (SELECT tbl_Companies.fld_company_e_mail
FROM tbl_Companies
WHERE itbl_company_person.fld_company_id.person_id=tbl_Companies.
fld_company_id)

I tried to execute it in SQL server, but I get the message
"Msg 258, Level 15, State 1, Line 7
Cannot call methods on int."

Does anyone know what I'm doing wrong?

Thanks.

 

[jal]

This query is confusing. Wouldn't it be easier to write it using a JOIN?

 

[jal]

Maybe something like this:

UPDATE itbl_company_person as I
INNER JOIN tbl_Companies as C
ON I.person_id=C.fld_company_id
SET fld_preferred_email =
NZ(fld_personal_email, C.fld_company_e_mail)


If the company_Email is a constant value, there possibly isn't a need for a join.

 

[bifteki]

Thank you for your answer.

When I run the query you wrote, I get the following message:

"Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'INNER'."

 

[bifteki]

The fld_company_e_mail field is not a constant value, it's different for every company (this means different for every record of the tbl_Companies table.

 

[jal]

Sorry, didn't realize SQL server. Every DB is different in syntax, I was thinking Access. I don't know if I have my Sql Server notes with me...

 

[jal]

Someone posted this example at Egghead cafe. Maybe we can translate it to your tables:

UPDATE A SET ID = B.ID, Col1 = B.Col1... and so on
FROM Table1 A
INNER JOIN Table2 B ON A.ID = B.ID AND A.Col3 = B.Col3 AND A.Col7 = B.Col7

 

[jal]

Maybe this ?

UPDATE itbl_company_person
SET fld_preferred_email =
Coalesce(fld_personal_email, C.fld_company_e_mail)
FROM itbl_company_person I
INNER JOIN tbl_Companies C
ON I.person_id=C.fld_company_id

 

[jal]

Or maybe

UPDATE I
SET fld_preferred_email =
Coalesce(fld_personal_email, C.fld_company_e_mail)
FROM itbl_company_person I
INNER JOIN tbl_Companies C
ON I.person_id=C.fld_company_id

 

[bifteki]

Thank you for your help Jal.
FYI, the syntax that finally worked is the following:

UPDATE itbl_company_person
SET fld_preferred_email =
(SELECT ISNULL (fld_personal_email,tbl_Companies.fld_company_e_mail))
FROM itbl_company_person
INNER JOIN tbl_Companies
ON itbl_company_person.fld_company_id=tbl_Companies .fld_company_id

Basically the last suggestion you made, only I used the ISNULL function.

 

[jal]

Thank you for your help Jal.
FYI, the syntax that finally worked is the following:

UPDATE itbl_company_person
SET fld_preferred_email =
(SELECT ISNULL (fld_personal_email,tbl_Companies.fld_company_e_mail))
FROM itbl_company_person
INNER JOIN tbl_Companies
ON itbl_company_person.fld_company_id=tbl_Companies .fld_company_id

Basically the last suggestion you made, only I used the ISNULL function.

Good for you. Glad you got it working. Now I am wondering what the difference is between IsNull and Colaesce. I'll have to look that up.

Also, I didn't know you could put a SELECT in that part of the query (seeing as there is no FROM clause that goes with it). I would have thought not

(SELECT ISNULL (fld_personal_email,tbl_Companies.fld_company_e_mail))


but rather have it with no SELECT:


(ISNULL (fld_personal_email,tbl_Companies.fld_company_e_mail))


or in my earlier syntax:


COALESCE (fld_personal_email,tbl_Companies.fld_company_e_mail)

And if my syntax wasn't working, I am curious to know why. Guess I still have a lot to learn about Sql Server. But that's my problem, not yours. Glad it's working for you.

 

[jal]

Well, I made some progress. Coalesce and IsNull are basically the same idea:

http://databases.aspfaq.com/database/coalesce-vs-isnull-sql.html