a conditional insert, is it possible?

[W3Falcon]

In a query like this:insert into "table1" ("field1") values ('" & 1 & "') where "field2" in (select "field3" from "table2" where "field3" like '881090%')*field2 from table1 = field3 from table2 (for example: "telephone numbers")*in table1 we have a list of all telephone numbers. In table2 we have the telephones assigned to clients.(only a few of the total).If the telephone of the client is in use, i want to assign a number "1" to a control field in table1 and if it´s not in use (not exist in table2) i want to assign a number "0" in the control field.I think the query i wrote, works. With other query i can assign the number "0" to the telephones that not in use.My question: Can i do all in the same query?

[Kcarson]

So you want to either use the query you wrote if the number is not in use, and if it is in use, assign a 1 to a separate field, correct?Also, what database are you using, MS SQL, Oracle, MySQL, Access, or something else?My guess is you can do a conditional statement (if/else) using the appropriate language for the database, Transact for MS SQL, PL/SQL for Oracle, and the others have similiar capapbilities.At that point you could place all of this in a stored procedure so that you only call one thing in your code but it would execute all of it.Well, clarify the question and provide the database being used, and if you can also include the method you are using it (VB, PHP, ASP, etc.) then hopefully we can answer your question better.

[aspnetguy]

Or you could just check the Database values before you execute the statemnt in your server code...not as fancy as a Stored Procedure but maybe easier if you are not familiar with them.

[Kcarson]

Or you could just check the Database values before you execute the statemnt in your server code...not as fancy as a Stored Procedure but maybe easier if you are not familiar with them.

<{POST_SNAPBACK}>

Definitely a valid point, there is always more than one way to achieve the same results in programming.

[vijay]

Hi, There is one thing Like TriggerU can use that according to your requirement And the other things Check at frontend some criteria what ever u wantif they are met then and then only u'll have to insert record in databseOk?Best Luck

[W3Falcon]

Thank you, for the interest.Really i am a newbie in this questions. Sorry if my questions are simples or the language incorrect ( i am spanish)The answers:I´m working with Asp Net(microsoft visual studio net 2003), Vbscript and i´m using Sql server2000 enterprise(with XP).I make a couple of stored procedures, but...before using it i´ll prefer try with the code in Asp Net.In conclusion:I have a table with telephones (the telephones in use). I am going to create a new table with all telephones, including "the telephones in use". And, with a select i want to put a "0" to telephones free, and "1" to the others.(with the mentioned "insert"(based in the "select" of the existing table).I can do it in two steps, with the asp. But i had the impression that it was possible to do this only with Sql in one query. May be i was wrong. Only want to satisfy my curiosity.

[Kcarson]

But i had the impression that it was possible to do this only with Sql in one query. May be i was wrong. Only want to satisfy my curiosity.

<{POST_SNAPBACK}>

I am not aware of any ways to do this, and I would be curious as well if there was one. As I mentioned, it is definitely possible to do in SQL, but not in one query to the best of my knowledge, because it would require a conditional statement as well (if/else).Although, since all the telephones in use are in one table, you might be able to do something like this:

Insert Into AllTelephones (TelePhoneNum, InUseFlag)Select TelePhoneNum, 1from Alltelephones, TelephonesInUsewhere alltelephones.telephoneNum = TelephonesInUse.Telephonenum

Then,

Update AllTelephonesSet InUseFlag = 0where InUseFlag <> 1

Still not just 1 query, but it would work..I think at least And you could create a trigger so that anytime you add numbers into the TelePhonesInUse Table it would automatically update the record in the AllTelephones table.That would be done something like this:

create trigger PhoneInUse on TelePhonesInUsefor insertas Update AllTelephones    Set InUseFlag = 1    From TelephonesInUse Where *Some Condition to distinguish last inserted record (probably a max function)*

Hope that helps some at least