I need help with a query.
basically I have tables that are related. Table A and Table B. Table B is the table where I am add or updating values that are related to table A.
I only want the save sql to only save to the row I modified. Currently the if table A more than one value related to the same unique ID is updating all the rows that
is related to the unique ID, which is not correct.
Example of whats going on:
Table A
| ID | Color | fk_ColorID |
| 1 | Blue | 2 |
| 2 | Red | 2 |
| 3 | Green | 3 |
| 4 | Purple | 3 |
Table B: This is the transaction table, where I am saving comments and relating the comments
to table A etc.... etc..... If you notice I have only updated one row,
| ID | Comments | IsActive | fk_ColorTableAID |
| 1 | Like Color | TRUE | 2 |
| 2 |
|
| 2 |
| 3 |
|
| 3 |
| 4 |
|
|
|
Output Inner Join Table. With the query I am using to save the data its updating rows that have the same
k_ColorTableAID value which is not correct. It should only update that particular row.
| ID | Color | fk_ColorID | Comments | IsActive | fk_ColorTableAID |
| 1 | Blue | 2 | Like Color | TRUE | 2 |
| 2 | Red | 2 | Like Color | TRUE | 2 |
| 3 | Green | 3 | | | |
| 4 | Purple | 3 | | | |
SQL Query I am using. Can someone help with the below sql to only the
ALTER PROCEDURE dbo.addOrUpdate
(
@Comments varchar(255),
@IsActive bit,
@fk_ColorTableAID decimal (18,0)
)
AS
DECLARE @FOUND integer
SET @FOUND = (SELECT COUNT(*) FROM Table B Where fk_ColorTableAID = @fk_ColorTableAID)
if @FOUND = 0
BEGIN
Insert into Table B
Values ( @Comments, @IsActive, @fk_ColorTableAID)
END
ELSE
BEGIN
UPDATE BomClosure
SET Comments = @Comments, IsActive = @IsActive
WHERE fk_ColorTableAID = @fk_ColorTableAID
END
RETURN
Result table should only update the active row on the grid, not where other related values
|
| ID | Color | fk_ColorID | Comments | IsActive | fk_ColorTableAID |
| 1 | Blue | 2 | Like Color | TRUE | 2 |
| 2 | Red | 2 |
|
|
|
| 3 | Green | 3 | | | |
| 4 | Purple | 3 | | | |