0

I have to do several updates based in a select result, today i do this:

//this will return an array... or no :-)
result = SELECT * FROM <table> where <condicion>

foreach (result as value){
    UPDATE <table> SET <column> = <something> WHERE <column> = value
}

Well, i want to pass the responsibility for the integrity (yes, this is important to this software) to database, i have been studied 'triggers','storage procedures' and 'transaction' but i didn't solved my issue, is that even possible?

Marcelo Filho
  • 1
  • 1
    You can build a single UPDATE statement that will handle that in one call, but it's hard to demonstrate with that pseudocode as the starting point. Can you update with something closer to your actual code? – Joel Coehoorn Sep 24 '13 at 13:56
  • 2
    Can you please explain your question and you problem? – NCA Sep 24 '13 at 13:58
  • This software have to know if your(s) parent is online of offline, this is dei=fined by users, the system use a boolean store this config. – Marcelo Filho Sep 24 '13 at 14:03
  • This software have to know if your(s) parent is online or offline, these are defined by users, the system use a boolean to store this config. the logic is if a category is disabled, the sub-category, the products and acessories attached to him don't have to appears on the searches. – Marcelo Filho Sep 24 '13 at 14:09
  • 1
    What DBMS are you using? – GarethD Sep 24 '13 at 15:13
  • The database is MySQL – Marcelo Filho Sep 24 '13 at 16:41

1 Answers1

2

Have a look at the UPDATE FROM syntax in SQL.

Various examples here

SteveB
  • 1,474
  • 1
  • 13
  • 21
  • This Assumes that the OP is using SQL-Server. `UPDATE FROM` is not standard SQL and there are some pretty strong arguments against using it in [this blog](http://sqlblog.com/blogs/hugo_kornelis/archive/2008/03/10/lets-deprecate-update-from.aspx). The standard SQL approach would be to use: `UPDATE Table SET Column = (SELECT Something FROM AnotherTable WHERE ) WHERE ...`, or using `MERGE`. – GarethD Sep 24 '13 at 15:14
  • Indeed. Of course there is always more than one way to skin a cat, as they say. [interesting discussion on the two approaches here]http://stackoverflow.com/questions/2334712/update-from-select-using-sql-server – SteveB Sep 24 '13 at 15:32
  • My main point was not that there is more than one way of doing this, it was that no DBMS has been tagged in the question and `UPDATE FROM` **ONLY** works in SQL-Server, and not in every other DBMS so there is a reasonable probability this is not the correct answer (and it is not as you say "*an SQL command*"). So I suggested two ANSI compliant methods to update using another table, in case the OP is not using SQL-Server. – GarethD Sep 24 '13 at 15:44
  • Sure. Although `UPDATE FROM` is likely to point the OP in the right direction. – SteveB Sep 24 '13 at 15:51