Insert or update a record if it already exists?

Dialect: SQL 92
I am afraid there is no sql92 answer to this question.
For sq192 an approach would be to insert every row and use grouping functions for reporting.

Dialect: T-SQL
How about this:

SQL:

if (exists (select * from AA_TestTable as t1

  where t1.ord_num = 'FFF'))

begin

  update AA_TestTable

  set ord_qty = 999

  where ord_num = 'FFF'

end

else

begin

  insert into AA_TestTable (ord_num, top_assy, ord_qty)

  values('GGG', 'XYZ', 567)

end 

Dialect: Oracle

SQL:

merge into users U1

using (U1.username = 'Jo' )

when matched then 

    update set U1.email = 'jo@email.com'

when not matched then 

     insert(U1.email, U1.username)

      values('Jo', jo@email.com);

SQL:

INSERT INTO users (username)

SELECT 'Jo' 

WHERE 'Jo' 

  NOT IN (SELECT username FROM users) 



UPDATE users 

SET email = 'jo@email.com'

WHERE username = 'Jo'

I use this method a lot. Obviously, 'Jo' would usually either be a variable or a field selected from another table.

This opption worked wonders! Thanks for posting

(I assume there is a typo in the last statement, where ord_num should be set to 'FFF').

You should be VERY CAREFUL with things like this. If you can't afford to set the transaction isolation level to SERIALIZABLE, some other transaction could add the row with ord_num='FFF' after you've tested for its existence, but before you've inserted it. This way you end up with violated primary constraint and error in one of these transactions.

This is a variation that works for tables with multiple primary keys.
If you have a users table with columns of username, dept, and age, and primary keys of username and dept, then this will only insert a user if it doesn't exist already.

SQL:

INSERT INTO users (username, dept, age) SELECT username='mp', dept='tax', age=5 WHERE (SELECT COUNT (*) FROM users WHERE username='mp' AND dept='tax')=0;

I initially found this syntax confusing, but here's how I 'parsed' it.
This part returns 0 if it doesn't exist:

SQL:

SELECT COUNT (*) FROM users WHERE username='mp' AND dept='tax'

This part creates a record-like row with static values and represents the record to insert:

SQL:

SELECT username='mp', dept='tax', age=5 

The WHERE-clause following the above part will only return the value to insert if the count is 0.

I'm trying to make a DTS package to run nightly to update my website table from another data source.

I want to use my employee table as an example to get this working, because it is small (about 345 records). Once I learn how to do this, I can apply it to my other needs. My data source is called "dbo.Employee". My destination is called "dbo.PV_Employee".

I need to query each row of PV to see if it is in webdatabase. If it is not in there, I want to insert the row. If it is in there, I want to update it, but only if it has been edited in the last 7 days.

All field names are the same in both tables. The ones I am working with are called:

~PV_EMPLOYEE_ID (this is the unique id that auto-generates a number)
~Employee_Id (binary)
~Active
~First_Name
~Last_Name
~Rn_Create_Date
~Rn_Edit_Date

Looking at some examples above, would I do something like this:

IF (EXISTS
(
SELECT * FROM dbo.PV_Employee AS Web
WHERE Web.Employee_Id = '???'
))
begin
UPDATE dbo.PV_Employee
SET Active = '???'
First_Name = '???'
Last_Name = '???'
Rn_Create_Date = '???'
Rn_Edit_Date = '???'
WHERE Employee_Id = '???'
end
ELSE
begin
INSERT INTO dbo.PV_Employee
( Employee_Id, Active,
First_Name, Last_Name,
Rn_Create_Date, Rn_Edit_Date )
VALUES
( '???', '???'
, '???', '???'
, '???', '???' )
end


Where I am showing the ??? marks above is where I think I am stumped. I use Coldfusion on my website and I know that when I want to put a value in there, it would look something like: SET Active = '#Active#' ...but I don't know how to output the value (or even where I get the value) from the dbo.Employee table.

Any help is appreciated, and please keep in mind I may appear to know something, but it is very limited (you can talk to me like I know nothing and that is fine).

Thank you in advance