0

Is it possible to combine both update and delete in a single SQL statement? I would like to execute something like this:

update a
set a.value = a.value + b.value
from Table1 a
join Table2 b on a.ID = b.ID

Also, rows, which has value higher than some constant number, should be deleted.

1

You may be able to arrange this in one statement using MERGE, adding the limiting maximum clause to the matching rule (the condition in using <source> on <condition>), and using WHEN NOT MATCHED BY SOURCE DELETE. See http://technet.microsoft.com/en-us/library/bb522522(v=sql.105).aspx for more detail on MERGE.

Performing the two operations in a transaction would make your code clearer though, so that would probably be the better option. Also note that WHEN NOT MATCHED BY SOURCE DELETE is not part of any standard for the MERGE statement (it is an MS SLQ Server extension) so if portability is a concern that needs to be considered.

|improve this answer|||||
1

Using procedure or trigger for this type of problem is better i think

PROCEDURAL WAY

CREATE PROCEDURE UPDATE(IN Required_params DATA_TYPE())
   BEGIN

    DECLARE EXIT HANDLER FOR sqlexception
    BEGIN
        SET output = 0;
        ROLLBACK ;
    END;

   START TRANSACTION;

    UPDATE a SET a.value = a.value + b.value FROM Table1 a JOIN Table2 b ON    a.ID = b.ID ;

    DELETE  FROM table1 where some_row>const;

  COMMIT ;
END

OR USING TRIGGER

CREATE TRIGGER `dele`
AFTER UPDATE ON `table1` FOR EACH ROW 

 BEGIN 
  SET SQL_SAFE_UPDATES=0;
  DELETE FROM `table1` WHERE some_row>const;
  SET SQL_SAFE_UPDATES=1;
 END
|improve this answer|||||
1

You can't combine them in one statement but you can wrap them into one transaction.

BEGIN TRANSACTION
  update a
  set a.value = a.value + b.value
  from Table1 a
  join Table2 b on a.ID = b.ID;

  DELETE FROM Table1 WHERE Table1.Value > @Constant;
COMMIT TRANSACTION ;
|improve this answer|||||
0

You could use MERGE command, something like this (this a dummy code, ok?):

MERGE YourTale AS pi
     USING (SELECT col1,col2 
            FROM YourTable) AS src (col1,col2)
    WHEN MATCHED 
        THEN UPDATE 
    WHEN NOT MATCHED
        THEN DELETE

You can learn more about MERGE command here: http://msdn.microsoft.com/pt-br/library/bb510625.aspx

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.