INNER JOINs in an UPDATE - 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

SQL Server 2000 Forums

SQL Server Development (2000)

INNER JOINs in an UPDATE

Author

Topic

nickjones
Starting Member

16 Posts

Posted - 2003-06-25 : 09:49:55

When working in a project in access, I'd get away with a query such as;


UPDATE TableA
	INNER JOIN TableB ON TableA.A_B_ID = TableB.B_ID
      SET A_SomeField = 'updated' WHERE B_OtherField = 'updatethisone'

Now I've migrated to SQL Server I get the error

Incorrect syntax near the keyword 'INNER'.

Is there a SQL server equivilent of this?

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2003-06-25 : 09:59:04

Try this:

Update TBLA
SET Column = Value
FROM db.owner.TableNameA AS TBLA
INNER JOIN db.owner.TableNameB AS TBLB
ON TBLA.Key = TBLB.TableAKey

nickjones
Starting Member

16 Posts

Posted - 2003-06-25 : 10:14:32

Thanks, that works great.

However, what if I want to set a column in table b as well?

Update TBLA
SET TBLA.Column = Value1, TBLB.Column = Value2
FROM db.owner.TableNameA AS TBLA
INNER JOIN db.owner.TableNameB AS TBLB
ON TBLA.Key = TBLB.TableAKey

Gives 'Cannot use the column prefix 'TBLB'. This must match the object in the UPDATE clause 'TBLA'.'

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2003-06-25 : 10:24:19

It seems that you are trying to do too much in one UPDATE statment.


Update TBLA 
SET TBLA.Column = Value1, TBLB.Column = Value2
FROM db.owner.TableNameA AS TBLA -- no need for this line here
INNER JOIN db.owner.TableNameB AS TBLB 
ON TBLA.Key = TBLB.TableAKey

You are basically telling SQL Server to UPDATE the TBLA table, yet in the next line you are referring to a another table and that is why it gets confused. Also, your FROM clause is referring to the same table you are attempting to update!

Try and split your update statment as one per table.

nickjones
Starting Member

16 Posts

Posted - 2003-06-25 : 10:36:54

Yup, that's my point.

Basically, what I want to do is update fields in two tables, based on constraints of both tables, in one query. This is possible in access. But not possible in SQL Server? Very odd.

I agree in most cercumstances you could split this in to two queries which, although not as slick, would do the job. However in this circumstance it's not possible because the UPDATE changes the field in the WHERE clause. I'll re-state the question - because I over-simplified it initially;

In access I can do the following;

UPDATE TableA
INNER JOIN TableB ON TableA.A_KeyB = TableB.B_ID
SET TableA.Column = 'newvalue', TableB.Column = 'newvalue'
WHERE TableA.Column = 'oldvalue' AND TableB.Column = 'oldvalue' TableB.Date < '01/01/2002'

I can't replicate this in SQL Server. Note that this can't easily be split in to two queries because if I run one update on one table, I can't then check this constaint in the second update.

Hope that makes sense :)

Edited by - nickjones on 06/25/2003 10:41:48

nr
SQLTeam MVY

12543 Posts

Posted - 2003-06-25 : 12:08:19

No - you can only update a single table in a query.
You can make the update atomic by including in a transaction

begin tran
update tblA ....
if @@error <> 0
begin
rollback tran
return
end
update tblB ....
if @@error <> 0
begin
rollback tran
return
end
commit tran

>> Note that this can't easily be split in to two queries because if I run one update on one table, I can't then check this constaint in the second update.

The constraints should be set up in the database structure.
If you need to do any checking then this should be done before starting any updates.

==========================================
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.

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2003-06-25 : 12:37:46

Nick,

Microsoft Access does not support the ANSI SQL standard. This causes headaches for many different reasons - functionality you find with Access that is completely nonstandard and unavailable anywhere else (not just SQL Server mind you, but all the others; Oracle, DB2, etc), and functionality that just isn't there at all, like count( distinct {column}) and other ANSI constructs.

Moving from Access to a "real" database engine is frustrating because of this. But don't think for a moment that Access is in some ways superior to other engines; it's not a question of pros/cons but rather a question of robustness. Everybody outgrows it eventually.

Jonathan
{0}

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2003-06-25 : 13:44:27

quote:


Update TBLA 
SET TBLA.Column = Value1, TBLB.Column = Value2
FROM db.owner.TableNameA AS TBLA -- no need for this line here
INNER JOIN db.owner.TableNameB AS TBLB 
ON TBLA.Key = TBLB.TableAKey

Why no need for that line? With this syntax, you need it for the table alias to be recognized in the UPDATE clause. Not to mention it makes it extremely straightforward and readable as to the intent.

LarsG
Constraint Violating Yak Guru

284 Posts

Posted - 2003-07-02 : 10:08:11

Use of the from clause in an update statement is not ANSI SQL either. It will only work in Sybase and SQL server.

The most portable would be to use a scalar subselect in the set clause.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources