update and insert help

[futbwal15]

need some help with coding...i want to be able to search my table first off...i want to be able to update the table, ONLY if the record IS NOT ALREADY THERE. i dont know if a CASE command will work here or not....please help.

This is what I have, but it doesn’t quite work.
INSERT INTO "ripedata" (matteridstring)
select distinct
f.matteridstring
from contacts c, parties p, fileinfo f, listbox l, events e
where
c.contactid = p.contactid and
f.fileno = p.fileno and
f.filetype = l.id and
e.fileno = f.fileno and
f.isactive = 1 and
e.title like '%Converted to LL%' and
e.date >= '01/01/2007' and
f.matteridstring is not null

I dont want to insert existing data either

 

[gmmastros]

Here's how I would do it....

If Exists(Select * from Table Where....)
  Begin
    [green]-- The record exists, so update it[/green]
  End
Else
  Begin
    [green]-- The record does NOT exist, so INSERT it[/green]
  End

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[futbwal15]

any other ideas anyone?

 

[gmmastros]

Did I misunderstand your question?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[futbwal15]

im not quite sure how to implement it with what i had code wise.

 

[gmmastros]

Trust me when I say... "This is the best way to handle this situation". I suggest you try to implement it. If you have problems, then post your code here along with the error message you are getting and we'll try to help more.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[futbwal15]

syntax errors seem to be the problem as of now

 

[gmmastros]

Post the code and what the error message is. I'm sure we can help.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[futbwal15]

INSERT INTO "ripedata_old_2" (matteridstring)

select distinct

(case when

c.contactid = p.contactid and

f.fileno = p.fileno and

f.filetype = l.id and

e.fileno = f.fileno and

f.isactive = 1 and

e.title like '%Converted to LL%' and

e.date >= '01/21/2004' and

f.matteridstring is not null

then

f.matteridstring

end)

from contacts c, parties p, fileinfo f, listbox l, events e



WHERE NOT EXISTS

(

SELECT f.matteridstring FROM fileinfo f, ripedata_old_2 r

WHERE f.matteridstring = r.matteridstring

)

there is the code....i am gettin syntax errors still

 

[futbwal15]

the code works now, however it is not providing me with the task i wanted...it returns 0 rows after execution....if i delete a record from my table and run the query again, it should return 1 row. any ideas?

 

[gmmastros]

This is a tough one because there are a lot of tables involved and I don't understand the relationships between the tables so I can't really advise you regarding which tables should be inner joined and which should be left joined.

I suggest you give this a try:

INSERT 
INTO   ripedata_old_2(matteridstring)
select distinct
       f.matteridstring
from   fileinfo f
       Left Join ripedata_old_2 r
         On f.matteridstring = r.matteridstring
       Inner Join parties p
         On f.fileno = p.fileno
       Inner Join contacts c 
         On c.contactid = p.contactid
       Inner Join listbox l
         On f.filetype = l.id
       Inner Join events e
         On e.fileno = f.fileno
where  f.isactive = 1 
       and e.title like '%Converted to LL%' 
       and e.date >= '01/01/2007' 
       and f.matteridstring is not null
       and r.matteridstring is null

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom