Help me with the UPDATE statement

• mw_sql_developer

  SSCoach

  Points: 19445

  More actions

  February 24, 2015 at 8:49 am

  #398069

  create table #a1 ( name varchar(100) , unit int, lab numeric ) ;

  Insert into #a1( name, unit ) values ( 'abc', 1 );

  Insert into #a1( name, unit ) values ( 'xyz', 1 );

  Insert into #a1( name, unit ) values ( 'a123', 1 );

  Insert into #a1( name, unit ) values ( 'zbx', 1 );

  create table #a2 (name varchar(100),lab numeric) ;

  Insert into #a2(name, unit ) values ( 'abc', 10.2);

  Insert into #a2(name, unit ) values ( 'abc', 3.2 );

  Insert into #a2(name, unit ) values ( 'abc', 6.4 );

  Insert into #a2(name, unit ) values ( 'abc', 7.4 );

  Insert into #a2(name, unit ) values ( 'abc', 8.2 );

  Insert into #a2(name, unit ) values ( 'abc', 3.1 );

  --I want table #a1 to have 6 rows that will have all the lab values for name 'abc'. Each row nust have the unit equal to 1

  --Finally the total number of rows in A1 would be 9

• mw_sql_developer

  SSCoach

  Points: 19445

  More actions

  February 24, 2015 at 8:56 am

  #1779619

  Sorry slight typo

  Here is the real question

  create table #a1 ( name varchar(100) , unit int, lab numeric ) ;

  Insert into #a1( name, unit ) values ( 'abc', 1 );

  Insert into #a1( name, unit ) values ( 'xyz', 1 );

  Insert into #a1( name, unit ) values ( 'a123', 1 );

  Insert into #a1( name, unit ) values ( 'zbx', 1 );

  create table #a2 (name varchar(100),lab numeric) ;

  Insert into #a2(name, lab ) values ( 'abc', 10.2);

  Insert into #a2(name, lab ) values ( 'abc', 3.2 );

  Insert into #a2(name, lab ) values ( 'abc', 6.4 );

  Insert into #a2(name, lab ) values ( 'abc', 7.4 );

  Insert into #a2(name, lab ) values ( 'abc', 8.2 );

  Insert into #a2(name, lab ) values ( 'abc', 3.1 );

  --I want table #a1 to have 6 rows that will have all the lab values for name 'abc'. Each row nust have the unit equal to 1

  --Finally the total number of rows in A1 would be 9

• mw_sql_developer

  SSCoach

  Points: 19445

  More actions

  February 24, 2015 at 8:59 am

  #1779621

  #a1 will need to have 6 rows for 'abc'

  and the rest of what was there ( 3 of them for the other members )

  So a total of 9 rows

• Lowell

  SSC Guru

  Points: 323518

  More actions

  February 24, 2015 at 9:16 am

  #1779627

  well, you can visualize the desired data with a join,

  /*--results

  name unit Lab

  ------- ----------- ------

  abc 1 10

  abc 1 3

  abc 1 6

  abc 1 7

  abc 1 8

  abc 1 3

  xyz 1 NULL

  a123 1 NULL

  zbx 1 NULL

  */

  SELECT T1.name,

  T1.unit,

  T2.Lab

  FROM #a1 T1

  LEFT JOIN #a2 T2

  ON T1.name = T2.name

  but to put the data into your #a1 table, you basically need to drop and replace with these results;

  it would be better if you described what it is you really want to do;

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• Nevyn

  SSChampion

  Points: 14210

  More actions

  February 24, 2015 at 9:19 am

  #1779630

  So you want #a1 to have six identical rows?

  It only has columns for name and unit, so all 6 would be ( 'abc', 1 ), correct?

  I'm somewhat puzzled as to why you would want to do this, and it will not be a simple update as rows will need to be inserted.

• mw_sql_developer

  SSCoach

  Points: 19445

  More actions

  February 24, 2015 at 9:27 am

  #1779635

  I want #a1 to have all the labs for abc.

  I don't care whether you do this via an UPDATE or INSERT or any combination.

• mw_sql_developer

  SSCoach

  Points: 19445

  More actions

  February 24, 2015 at 9:41 am

  #1779647

  Never mind, found my own solution..

  Here it goes....

  Of couse I used parts from some of the solutions you provided above.

  Thanks for the effort.

  Can close this ticket now.

  drop table #a1;

  drop table #a2;

  create table #a1 ( name varchar(100) , unit int, lab decimal(10,2) ) ;

  Insert into #a1( name, unit ) values ( 'abc', 1 );

  Insert into #a1( name, unit ) values ( 'xyz', 1 );

  Insert into #a1( name, unit ) values ( 'a123', 1 );

  Insert into #a1( name, unit ) values ( 'zbx', 1 );

  create table #a2 (name varchar(100),lab decimal(10,2)) ;

  Insert into #a2(name, lab ) values ( 'abc', 10.2);

  Insert into #a2(name, lab ) values ( 'abc', 3.2 );

  Insert into #a2(name, lab ) values ( 'abc', 6.4 );

  Insert into #a2(name, lab ) values ( 'abc', 7.4 );

  Insert into #a2(name, lab ) values ( 'abc', 8.2 );

  Insert into #a2(name, lab ) values ( 'abc', 3.1 );

  --I want table #a1 to have 6 rows that will have all the lab values for name 'abc'. Each row nust have the unit equal to 1

  --Finally the total number of rows in A1 would be 9

  --

  IF object_id('stagingtmp' ) IS NOT NULL

  EXEC('DROP TABLE stagingtmp');

  SELECT T1.name, T1.unit, T2.lab INTO stagingtmp

  FROM #a1 T1

  LEFT JOIN #a2 T2

  ON T1.name = T2.name

  TRUNCATE TABLE #a1;

  INSERT INTO #a1

  Select * FROM stagingtmp;

  IF object_id('stagingtmp' ) IS NOT NULL

  EXEC('DROP TABLE stagingtmp');

  Select * FROm #a1;