SQL Update Confusion

Can I join tables?

[Nick_W]

Hi all,

I'm trying to update tblA but I need to join it with tblB kind of like this:

UPDATE tblA, tblB SET tblA.somerow = 'someval' WHERE tblA.somthing=1 AND tbl.sothingelse=0 AND tblA.id = tblB.correspondingid

But although it looks like that's the way to do it from the manual, it's giving me errors on the "UPDATE tblA,tblB" bit so I guess i'm missing somthing?

Any pointers/help much appreciated....

Thanks

Nick

[dingman]

I've never actually done this, but I *think* what you need to do is something like

update <join-statement> set column-from-join = 'newAval' where <conditions>

The idea is that if you need a table to operate on, produced by the join, before you can update it. However, keep in mind that I'm spewing ideas based on vague reccolection of "relational algebra" from a theoretical relational database class I took three years ago. I have no idea how this plays out in a real RDBMS - theory implements all the features and has no bugs.

[jatar_k]

example from manual

UPDATE TableA, TableB SET Total = Total + Value WHERE TableA.SubTable = TableB.SubTable

hmm, is the error just "you have an error in your statement near blah"?

[coopster]

Which version of MySQL are you using?
<edit>Sorry, I assumed you were using MySQL</edit>

[Nick_W]

>>hmm, is the error just "you have an error in your statement near blah"?

Yes. Here's the actual statement with the names changed to protect the innocent ;)

UPDATE posts, postcontrols SET text = 'test text' WHERE postcontrols.parent = '9' AND postcontrols.start=1 AND postcontrols.postid = posts.id

and the error:

You have an error in your SQL syntax near ' postcontrols SET text = 'dddddddd' WHERE postcontrols.p' at line 1

>version
3.23

Thanks everyone...

Nick

[coopster]

Starting with MySQL Version 4.0.4, you can also perform UPDATE operations that cover multiple tables:

UPDATE items,month SET items.price=month.price
WHERE items.id=month.id;

[Nick_W]

Damn! Just beat me! - I had that section cut to paste here aswell ;) My thanks!

I guess that means I have to do a lookup then the update...

Nick