Syntax error on UPDATE with INNER JOIN

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

Syntax error on UPDATE with INNER JOIN

Author

Topic

Equalizer700
Starting Member

3 Posts

Posted - 2011-06-10 : 11:49:03

I am getting a 'syntax error near INNER' on this UPDATE statement:
UPDATE tbl_guests INNER JOIN tbl_organizations ON tbl_guests.organization_id = tbl_organizations.organization_id SET tbl_guests.prefix=@prefix, tbl_guests.fname=@Fname, tbl_organizations.organization_name=@OrgName, tbl_organizations.organization_city=@City, tbl_organizations.organization_zip=@Zip WHERE tbl_organizations.organization.state = @State AND tbl_guests.guest_type = 'liaison';

I am trying to update two joined tables. What is the proper syntax for this? Help is appreciated!

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-06-10 : 11:50:32

UPDATE tbl_guests
SET prefix = @prefix,
fname = @Fname
FROM tbl_guests
INNER JOIN tbl_organizations ON tbl_guests.organization_id = tbl_organizations.organization_id
WHERE tbl_organizations.organization.state = @State
AND tbl_guests.guest_type = 'liaison' ;

UPDATE tbl_organizations
SET organization_name = @OrgName,
organization_city = @City,
organization_zip = @Zip
FROM tbl_guests
INNER JOIN tbl_organizations ON tbl_guests.organization_id = tbl_organizations.organization_id
WHERE tbl_organizations.organization.state = @State
AND tbl_guests.guest_type = 'liaison' ;
--------------------------
http://connectsql.com/

Equalizer700
Starting Member

3 Posts

Posted - 2011-06-10 : 13:12:19

That'a awesome, your code works perfectly. Thanks!
I have never used a FROM in an UPDATE, but never tried joining on an UPDATE either. I googled FROM but did not get a clear explanation of why it is required here.

Now I need to make an INSERT work on the same two tables.

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-06-10 : 21:13:56

People cannot read your mind, so post your code and clear specs if you really want help. Please post real DDL and not narrative or your own personal programming language. Learn to use ISO-11179 rules for the data element names, avoid needless dialect and use ISO-8601 temporal formats, codes and so forth. Please tell us what SQL product and release you are using. Tell us if you can change the DDL or if you are stuck with it.

Please stop putting tbl- on table names; it not only violates ISO-11179, it also looks really silly.

Another thing good SQL avoid is the old Sybase UPDATE.. FROM syntax. It is not ANSI/ISO, makes no sense in the SQL data model and is subject to cardinality errors.

What you wrote is even worse. A JOIN exists only in the scope of its statmene6t. So even if you did update it, it would disappear immediately! Parameters are data elements; they have a role prefix (in_ or out_ or inout_) but they are still drawn from the same domain. They do not change names

You are updating two tables, so use two UPDATEs:

BEGIN TRANS

UPDATE Organizations
SET organization_name = @in_organization_name,
city_name = @in_city_nbamer,
zip = @in_zip
WHERE state_code = @in_state_code;

UPDATE Guests
SET prefix = @in_prefix,
first_name = @in_first_name,
WHERE guest_type = 'liaison'
AND EXISTS
(SELECT *
FROM Organizations AS O
WHERE Guests.organization_id = O.organization_id);
END TRANS;

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Equalizer700
Starting Member

3 Posts

Posted - 2011-06-13 : 09:18:38

Yikes, looks like the SQL Police are here!

Celko, I came here for help, not a lecture on my grammar or the finer points of obscure ISO specs.

Lionofdezert, thank you.

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-06-13 : 17:02:55

"obscure ISO specs" ? LOL! Like the SI units? Like SQL? And that is your idea of a professional attitude to this trade!

I did give you REAL help. You are not writing correct SQL; yo0u have no idea what the basic data model of SQL is; you need the lectures. Desperately.

"Against stupidity the gods themselves struggle in vain." - Die Jungfrau von Orleans; Friedrich von Schiller (1759-1805)

Make you a deal. Ask your boss to hire me for a week to clean up your code. If I can make measurable improvements in performance and bring the code up to Industry Standards, then he fires you and writes you a horrible recommendation to protect other companies. :)

This has happened twice, but it was someone's boss seeing the posting and contacting me rather than the bad programmer offering to put his job on the line.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources