0

I can do this:

SELECT team.name, state.name 
FROM team 
INNER JOIN state 
ON team.state_id = state.id;

Now, say Texas changes their name to Saxet so the Houston Texans change their name to the Houston Saxetians.

Can I do something like this:

UPDATE 
  (team INNER JOIN state ON team.state_id = state.id)
SET 
  state.name = "Saxet", 
  team.name = "Saxetians";

My main question is - can this be done? I'm pretty sure though that it cannot be done because I searched around and couldn't find anything. If it in fact cannot be done, could you please explain any insight as to why? Does this feature somehow oppose the fundamentals of SQL in a way that I don't grasp?

Thank you!

Nick Manning
  • 2,828
  • 1
  • 29
  • 50
  • 1
    Not in standard ANSI SQL (which is what the `sql` tag refers to). But different DBMS products have different extensions to the UPDATE syntax that would allow this. Which DBMS product are you using? –  Jul 02 '21 at 07:01
  • Does this answer your question? [How can I do an UPDATE statement with JOIN in SQL Server?](https://stackoverflow.com/questions/1293330/how-can-i-do-an-update-statement-with-join-in-sql-server) – Roman Hocke Jul 02 '21 at 07:03
  • You need to do two separate UPDATEs. As mentioned above, some DBMS can use joins in UPDATE, but are unable to update two tables at a time within that join. – Roman Hocke Jul 02 '21 at 07:06
  • @a_horse_with_no_name I am currently starting a project using sqlite3 but I can change it! – Nick Manning Jul 02 '21 at 07:07
  • 1
    @RomanHocke thanks...I did see that link and that seems to only get values from another table rather than update two at once. Yes it seems I need to make two separate queries based on what I've read so far. – Nick Manning Jul 02 '21 at 07:08
  • @a_horse_with_no_name do you happen to know what any of these other DBMS products are? I would consider switching over for this feature. – Nick Manning Jul 02 '21 at 07:45
  • 1
    I don't believe there is any RDBMS that supports updating rows in multiple tables with a single `update` statement, consider the locking and concurrency complications! – Stu Jul 02 '21 at 08:34
  • Where does the "i" come from in "Saxetian"? There is no "i" in "Texan" . . . or perhaps only `I`s (oh, I shouldn't make political statements ;). – Gordon Linoff Jul 02 '21 at 12:00
  • @Stu Hmmm...I'll be honest it's not intuitive to me why this would be problematic! In my example, SQL would just update the two fields from the two tables. But I don't have an in depth knowledge of how SQL works under the hood. – Nick Manning Jul 02 '21 at 12:40
  • 1
    @GordonLinoff lol...maybe it should be Sexatan – Nick Manning Jul 02 '21 at 12:42

0 Answers0