0

Please login or register to vote for this query.

(click on this box to dismiss)

Concerning question http://stackoverflow.com/q/8108798/939860

Q&A about the site for members of the workforce navigating the professional setting

-- Demo ahmd0 - rows switch
-- Concerning question http://stackoverflow.com/q/8103183/939860

CREATE TABLE #t1(
 id INT PRIMARY KEY
,name NVARCHAR(255));

CREATE TABLE #t2(
ord INT
,itmid INT PRIMARY KEY);

INSERT INTO #t1 VALUES
 (3    ,'John')
,(5    ,'Mary')
,(8    ,'Michael')
,(10   ,'Steve')
,(13   ,'Jack')
,(20   ,'Pete')

INSERT INTO #t2 VALUES
 (1    ,5)
,(4    ,8)
,(5    ,13)
,(8    ,3)
,(10   ,10)
,(13   ,20);

WITH x AS (
  SELECT t2.itmid, t2.ord
  FROM   #t1 t1
  LEFT   JOIN #t2 t2 ON (t1.id = t2.itmid)
  WHERE  t1.name = 'Pete'  -- must be unique!
  ), y AS (
  SELECT t2.itmid, t2.ord
  FROM   #t1 t1
  LEFT   JOIN #t2 t2 ON (t1.id = t2.itmid)
  WHERE  t1.name = 'Steve'  -- must be unique!
  )
UPDATE #t2 SET ord = z.ord
FROM (
SELECT x.itmid, y.ord FROM x,y
UNION ALL
SELECT y.itmid, x.ord FROM x,y
   ) z
WHERE #t2.itmid = z.itmid

SELECT * FROM #t1 JOIN  #t2 ON #t2.itmid = #t1.id

fork query

created nov 13 11 Erwin Brandstetter