Update Statement Help - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Update Statement Help

Author

Topic

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2011-09-13 : 07:57:26

Hi All

I have an update statement I am using in Access and would like to get it proof read. I have one column that I would like to update and the conditions come from another table that is connected via a link table, this table has a condition in one column and a condition on another column I would like to compare and then update but I am not sure of my update query is correct.

I ran the query as a select statement and it seemed to bring back the records I was wanting to update fine but am unsure of the syntax of the two set statements as I am using SET WHERE twice.


UPDATE( SELECT C1.ReferredBy AS Ref, C3.Title AS T, C3.ContactType as CT
                FROM ((tblCompanies C1 
                INNER JOIN companycontacts C2 ON C1.CompanyID = C2.CompanyID)
                INNER JOIN tblContacts C3 ON C2.ContactID = C3.ContactID) 
                WHERE C3.Title = 'Condition1' OR C3.ContactType = 'Condition2') AS D
SET D.Ref = 'Condition1'
WHERE D.T = 'Condition1',
SET D.Ref = 'Condition2'
WHERE D.CT = 'Condition2';

Does it look like there is a better way to do this?

G

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-13 : 08:03:58

[code]
UPDATE D
SET D.Ref = CASE WHEN D.T = 'Condition1' THEN 'Condition1'
WHEN D.CT = 'Condition2' THEN 'Condition2'
END
FROM
( SELECT C1.ReferredBy AS Ref, C3.Title AS T, C3.ContactType as CT
FROM ((tblCompanies C1
INNER JOIN companycontacts C2 ON C1.CompanyID = C2.CompanyID)
INNER JOIN tblContacts C3 ON C2.ContactID = C3.ContactID)
WHERE C3.Title = 'Condition1' OR C3.ContactType = 'Condition2') AS D
WHERE D.T = 'Condition1' OR
D.CT = 'Condition2'
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2011-09-13 : 08:15:37

Ok thanks will give that a go.

G

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-13 : 08:27:14

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2011-09-13 : 09:18:19

What if i am using in access, case statement doesn't seem to work.

G

Kristen
Test

22859 Posts

Posted - 2011-09-13 : 10:14:06

In Access its maybe IF(Condition,TrueValue,FalseValue) or perhaps IIF(...)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-13 : 10:32:34

i think its IIF

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2011-09-13 : 11:22:44

I tried using an IF in place of CASE statement and still get a 'Syntax error missing operator in query expression' with code below

UPDATE D
SET D.Ref = If D.T ='Condition1' THEN 'Condition1'
Else 'Condition2'

FROM ( SELECT C1.ReferredBy AS Ref, C3.Title AS T, C3.ContactType as CT
FROM ((tblCompanies C1
INNER JOIN companycontacts C2 ON C1.CompanyID = C2.CompanyID)
INNER JOIN tblContacts C3 ON C2.ContactID = C3.ContactID)
WHERE C3.Title = 'Condition1' OR C3.ContactType = 'Condition2') AS D
WHERE D.T = 'Condition1' OR D.CT = 'Condition2'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-13 : 11:25:55

try


UPDATE D
SET D.Ref = IIF( D.T ='Condition1' , 'Condition1', 'Condition2')
FROM ( SELECT C1.ReferredBy AS Ref, C3.Title AS T, C3.ContactType as CT 
FROM ((tblCompanies C1 
INNER JOIN companycontacts C2 ON C1.CompanyID = C2.CompanyID) 
INNER JOIN tblContacts C3 ON C2.ContactID = C3.ContactID)
WHERE C3.Title = 'Condition1' OR C3.ContactType = 'Condition2') AS D 
WHERE D.T = 'Condition1' OR D.CT = 'Condition2'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2011-09-13 : 11:34:59

quote:
Originally posted by visakh16

try

UPDATE D
SET D.Ref = IIF( D.T ='Condition1' , 'Condition1', 'Condition2')
FROM ( SELECT C1.ReferredBy AS Ref, C3.Title AS T, C3.ContactType as CT
FROM ((tblCompanies C1
INNER JOIN companycontacts C2 ON C1.CompanyID = C2.CompanyID)
INNER JOIN tblContacts C3 ON C2.ContactID = C3.ContactID)
WHERE C3.Title = 'Condition1' OR C3.ContactType = 'Condition2') AS D
WHERE D.T = 'Condition1' OR D.CT = 'Condition2'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Hi Just tried that it still gives same error!

Here is my exact code with the conditions to see if anyone can spot mistakes:

UPDATE D
SET D.Ref = IIf(D.T = "Sgt.", "Sgt.", "Trainer")
FROM
(SELECT C1.ReferredBy AS Ref, C3.Title AS T, C3.ContactType as CT
                ((FROM tblCompanies C1 
                INNER JOIN companycontacts C2 ON C1.CompanyID = C2.CompanyID)
                INNER JOIN tblContacts C3 ON C2.ContactID = C3.ContactID)
                WHERE C3.Title = "Sgt." OR C3.ContactType = "Trainer") AS D
WHERE D.T = "Sgt." OR D.CT = "Trainer";

Does the use of single or double quotes matter? It doesn't seem to make any difference to the error.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-13 : 11:36:12

try posting it in access forums then

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2011-09-14 : 05:46:29

Ok in the spirit of helping others the access update statement does not use a FROM clause in it. This code below now works:

UPDATE tblContacts AS C1 INNER JOIN (tblCompanies AS C2 INNER JOIN CompanyContacts AS C3 ON C2.CompanyID=C3.CompanyID) ON C1.ContactID=C3.ContactID SET C2.ReferredBy = SWITCH(C1.Title='Condition1','Condition1',C1.ContactType='Condition2','Condition2')
WHERE (((C1.Title)='Condition1')) OR (((C1.ContactType)='Condition2'));

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources