Update Rows across tables sequentially

• rishakin

  SSC Rookie

  Points: 27

  More actions

  September 26, 2016 at 11:38 pm

  #97483

  Hi,

  Problem Statement:

  I have 2 tables say Table1 and Table2. They both have fields Number and type. But the numbers across the table should be reordered sequential when user clicks a button on the UI.

  Table1 say has numbers 1,2,3,80 and

  Table2 say has numbers 12,58,70,92

  Expected result should be

  Table1 say should have numbers 1,2,3,7 and

  Table2 say has numbers 4,5,6,8

  How to update Table1 and Table2 data with these numbers?

  PS: if you combine in ascending order : 1,2,3,12,58,70,80,92 so we need to sequence the numbers

  This SQlite statement will give the result of combined data in ascending order

  SELECT Number, Type,"IoCircuit" As TableName FROM IoCircuit

  where (IoCircuit.Type = "CONVLOOP" OR IoCircuit.Type = "MMIN")

  union

  SELECT Number,Type,"Circuit" As TableName FROM Circuit

  where (Circuit.Type = "CONVLOOP")

  Not sure how to update across tables:

  Update Table? Set not sure how to go about. Temporary table will not help as I have to anyways update Table1 and Table2

  Thanks for any help stuck in this issue for almost 2 days now.

  -Ranjita

• Eirikur Eiriksson

  SSC Guru

  Points: 182947

  More actions

  September 27, 2016 at 12:47 am

  #1903259

  Quick questions, are you using SQLite or SQL Server? Can you post the DDL (create table ) scripts for the relevant tables, sample data as an insert statement and the expected results. Alternatively to the DDL you could post the .sqlite file in a zipped format although if you are on SQLite then you might not get too much input on this SQL Server forum.

  

• Thomas Rushton

  SSC-Insane

  Points: 22649

  More actions

  September 27, 2016 at 5:55 am

  #1903299

  I tried doing the old updateable view thing, but that wouldn't work because of a union in the view definition...

  Here's a suggestion...

  You'll need to create a temporary table (or table variable) as a middle ground for sorting, and then update the base tables. Here's some sample / demo code that shows the solution I have in mind:

  USE tempdb;

  CREATE TABLE t1 (id INT, description varchar(MAX));

  CREATE TABLE t2 (id INT, description VARCHAR(MAX));

  GO

  INSERT INTO t1 VALUES (1,'a'),(2,'b'),(3,'c'),(80,'eighty');

  INSERT INTO t2 VALUES (12, 'twelve'),(58,'58'),(70,'seventy'),(92,'92');

  DECLARE @ttemp TABLE (id INT, description VARCHAR(MAX), rownum INT NULL) -- or use a temporary table...

  INSERT INTO @ttemp (id, description, rownum)

  SELECT foo.id, foo.description, ROW_NUMBER() OVER (ORDER BY foo.id) FROM (

  SELECT id, description FROM t1

  UNION ALL

  SELECT id, description FROM t2 ) foo;

  --SELECT * FROM @ttemp

  UPDATE t1 SET id = ttemp.rownum

  FROM @ttemp ttemp

  WHERE dbo.t1.id = ttemp.id

  UPDATE t2 SET id = ttemp.rownum

  FROM @ttemp ttemp

  WHERE dbo.t2.id = ttemp.id

  SELECT * FROM dbo.t1

  SELECT * FROM dbo.t2

  Thomas Rushton
  blog: https://thelonedba.wordpress.com

• drew.allen

  SSC Guru

  Points: 77012

  More actions

  September 27, 2016 at 10:18 am

  #1903403

  rishakin (9/26/2016)

  ---

  Hi,

  Problem Statement:

  I have 2 tables say Table1 and Table2. They both have fields Number and type. But the numbers across the table should be reordered sequential when user clicks a button on the UI.

  Why do they need to be sequential? You can already order by them.

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA