David Smith

David Smith

  • NA
  • 2k
  • 0

SQL Query Help

Jul 7 2011 10:49 AM
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

IDColorfk_ColorIDCommentsIsActivefk_ColorTableAID
1Blue2Like ColorTRUE2
2Red2


3Green3   
4Purple3   


Answers (3)