Select as variable SQL

Question

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?

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
– Joel Coehoorn, Commented Sep 24, 2013 at 13:56
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
– Marcelo Filho, Commented Sep 24, 2013 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
– Marcelo Filho, Commented Sep 24, 2013 at 14:09

SteveB · Accepted Answer · 2013-09-24 15:51:44Z

2

Have a look at the UPDATE FROM syntax in SQL.

Various examples here

edited Sep 24, 2013 at 15:51

answered Sep 24, 2013 at 13:54

SteveB

1,5141 gold badge13 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

GarethD Over a year ago

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. The standard SQL approach would be to use: UPDATE Table SET Column = (SELECT Something FROM AnotherTable WHERE <link condition>) WHERE ..., or using MERGE.

SteveB Over a year ago

Indeed. Of course there is always more than one way to skin a cat, as they say. [interesting discussion on the two approaches here]stackoverflow.com/questions/2334712/…

GarethD Over a year ago

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.

SteveB Over a year ago

Sure. Although UPDATE FROM is likely to point the OP in the right direction.

Collectives™ on Stack Overflow

Select as variable SQL

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related