473,238 Members | 1,691 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,238 software developers and data experts.

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

1 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!
Feb 17 '21 #1
1 3478
NeoPa
32,554 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.
Feb 18 '21 #2

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

Similar topics

0
by: Chris Nighswonger | last post by:
------=_NextPart_000_0013_01C352C0.6B0A6E30 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Hi All, Is it possible in MySQL to use UPDATE to update...
3
by: uw_badgers | last post by:
Is it possible to create a unique constraint to a column from another table? For example: tb_current: current_names -------------- aaa bbb tb_new:
1
by: bpforte | last post by:
Hello, I need help with building query, basically I need to select all records from one table that don't exists in second table with status 1, but they can exists in second table with status 0, to...
3
by: wvmbark | last post by:
First time poster... I just found this forum and it appears there's plenty of people here that could make short work of problem that's been driving me absolutely bonkers for months. Every day we...
3
by: kchang77 | last post by:
Good morning. I have two tables (Batches) and (Archive). (Batches) is a linked table that contains all of the existing data and the other (Archive) I am keying data into, through a form. I want...
1
by: suma2006 | last post by:
Hi All, I have one table namely app_users with fields(username,password,emai,phno,address) and one more table add_users with fields (uname,name,phno,email,status). If email address is already...
2
by: scoots987 | last post by:
Hi all, sorry if this is the wrong place to put this. I have two tables, both contain address info. I would like to update address1, address2, city, state, zipcode and country. May be a few...
4
by: BulbFresh | last post by:
Hi, I'm using Access 2007. I have imported a table called 'January' and from this I need to update an existing table called 'TablesInfo' which needs to hold some details about the imported...
4
Jerry911
by: Jerry911 | last post by:
I have found multiple examples of VBA functions to test for the existance of a column in a table and getting a message return. My need is a bit different so I better try and give the sequence of...
5
by: Joel Marion | last post by:
I have a table that I want to analyze for multiple data points (true/false or 'exists in another table). Because I want to analyze more than one point, doing a join a showing Is Null won't work. At...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.