update with a select on 2 tables

[grimep]

Is it possible to do an update on one table, with a selection across 2 tables, or does one first have to query a table, then iterate through the rows doing an update on the other table per line (which seems v inefficient)?

I want to say something like

UPDATE table1, table2 SET table1.val = table1.val + 1 WHERE table1.userID = table2.userID AND table1.otherVal = table2.otherVal

is this possible? or do I have to SELECT from table2, and then iterate through the resultset updating table1?

 

[jrbarnett]

I have just tried this and it works in Access (not Ansi SQL) but don't have other databases to hand to test it out on.

John

 

[swampBoogie]

One way of writing that using standard SQL is

UPDATE table1
   SET table1.val = table1.val + 1 
 WHERE exists 
   (select * 
      from table2
     where table1.userID = table2.userID 
       AND table1.otherVal = table2.otherVal)

 

[grimep]

neither solution works in mySQL.

in the original style..
UPDATE table1
SET table1.val = table1.val - 1

works, but as soon as you introduce a 2nd table it doesnt

UPDATE table1, table2
SET table1.val = table1.val - 1

<no joy>

Thanks for the help swampboogie, that looks like it should work to me, but it doesn't. Another long night then!

 

[swampBoogie]

Mysql does not support subqueries.

The syntax in your first post should work in version 4.04 or later (according to the docs, which seems to be slightly unreliable sometimes).

 

[fluteplr]

You might want to post this in the MySQL forum. Versions of MySQL prior to the current version support such a small subset of ANSI sql that most solutions posted here will not work.

 

[grimep]

ah right, I had read some of its limitations in the manual.

Sorry, hadn't noticed a mySQL forum here - though the answers I've received have been really helpful, I did a relational databases course 3 years ago, SQL was one module out of several. The SQL part covered most of it but was fairly basi. I've got MsSQL and Sybase SQL Anywhere on here too..