Question on insert

[EdRev]

In my vbscript, I update a table using this sql pass-thru:

strsql = "and stores in("GAP","OLD NAVY","BANANA REPUBLIC","NEW STORE") and acct = "SALARY"

strsql1 = "update table1 set SalesP1 = 0, SalesP2 = 0, SalesP3 = 0 where "
strsql1 = strsql1 & " YEAR = '2007' " & strsql

Comm.commandtype = 1

Comm.commandtext = strsql1
Comm.execute

Since NEW STORE does not have any record yet, NEW STORE/SALARY does not get updated with 0.
One option for me is to do a delete and insert:


strsql1 = "delete from table1 where YEAR = '2007' " & strsql

Comm.commandtype = 1

Comm.commandtext = strsql1
Comm.execute

How do I handle the insert?

Any help will be greatly appreciated.

 

[Turkbear]

Hi,
Note: Your post would be much easier to decode if you placed the Sql strings in order.

update table1 set SalesP1 = 0, SalesP2 = 0, SalesP3 = 0 where YEAR = '2007' and stores in("GAP","OLD NAVY","BANANA REPUBLIC","NEW STORE") and acct = "SALARY"

Since, NEW STORE has no records, hence no acct='SALARY' , you will need to have the Sql test for the existance of a record and branch to an Insert statement ( which would include 'SALARY' as the value for acct, and 0 for the other fields) if none exists and to the Update statement if there is a record.

Since you did not mention the database, I cannot be more specific.







To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Turkbear]

Hi,
Sorry !!!
It is, after all, the Oracle 9i forum - duh!

Look at the MERGE clause in your Sql reference to find a way to do this in one step...







To Paraphrase:"The Help you get is proportional to the Help you give.."