Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #736

Re: trying to replace multiple update statements with one update

Show all headers | View raw

The two tricks are CASE expressions and the MERGE statements

While I am glad for your DDL, I wish it was valid. No keys, improper
data element names (a magical generic “id” and “seq” in SQL? No way!)
make it hard to read and use. Here is my guess at what you might have
meant. I cannot figure out what a “pid” is and I made up a key, even
tho “stop_id” is a superkey.

CREATE TABLE Stops
(stop_id INTEGER NOT NULL,
 stop_seq INTEGER NOT NULL,
 PRIMARY KEY (stop_id, stop_seq)
 pid INTEGER NOT NULL,
 stop_type INTEGER NOT NULL);

You do know about the ANSI/ISO row constructors? It savrs a lot of
dialect and typing:

INSERT INTO Stops
VALUES
(10, 1, 100, 0),
(10, 2, 100, 1),
(11, 3, 100, 0),
(11, 4, 100, 1);

UPDATE Stops
   SET stop_seq
       = CASE WHEN stop_id = 11 AND stop_type = 0 THEN 1
              WHEN stop_id = 11 AND stop_type = 1 THEN 2
              WHEN stop_id = 10 AND stop_type = 0 THEN 3
              WHEN stop_id = 10 AND stop_type = 1 THEN 4
              ELSE stop_seq END
 WHERE pid = 100;

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

trying to replace multiple update statements with one update migurus <migurus@yahoo.com> - 2011-10-03 18:11 -0700
  Re: trying to replace multiple update statements with one update Erland Sommarskog <esquel@sommarskog.se> - 2011-10-04 23:14 +0200
  Re: trying to replace multiple update statements with one update --CELKO-- <jcelko212@earthlink.net> - 2011-10-05 09:21 -0700
    Re: trying to replace multiple update statements with one update Erland Sommarskog <esquel@sommarskog.se> - 2011-10-05 23:44 +0200

csiph-web