467,921 Members | 1,324 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,921 developers. It's quick & easy.

Update if row exists otherwise add row from another table if equal to status

Bit
Hi,

I'm using SQL server and I'm trying to create a script to update my table if row exists, else insert into that table.
Let's call the table I want to add rows into SALES and my other PROJECT
Will always be a one-one relationship.

If a new project comes in, I want to create a SQL statement that checks if that project exists, if it does it should update a few columns of my sales table, but if it does not exists then add that record into my SALES table.

I have ProjectID, Client and Status in both tables.
I have tried something like this, but not gotten it right.
Expand|Select|Wrap|Line Numbers
  1. IF EXISTS (SELECT 1 FROM SALES INNER JOIN PROJECT
  2. ON (SALES.ProjectID = PROJECT.ProjectID 
  3. AND
  4. SALES.Status = PROJECT.Status))
  5.         BEGIN 
  6.             UPDATE SALES(ProjectID, Client, Status)
  7.         END
  8.  ELSE
  9.         BEGIN
  10.          INSERT INTO SALES(ProjectID, Client, Status)
  11.     Select ProjectID, Client, Status
  12.     from PROJECT
  13.     Where Status = 'Active' or Status = 'Expected' or ProjectType = 'Confirmed'
  14.        END
Any suggestions on how to solve this?

Thank you very much!
2 Weeks Ago #1
  • viewed: 1472
Share:
1 Reply
NeoPa
Expert Mod 16PB
It seems to me that you may be looking for the MERGE (Transact-SQL) statement.

To assist in your searching it may help to know this is also frequently referred to as UPSERT (Update / Insert) in general parlance.
2 Weeks Ago #2

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

reply views Thread by Chris Nighswonger | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.