?
Solved

SYbase

Posted on 2011-03-10
7
Medium Priority
?
312 Views
Last Modified: 2012-05-11
I have a table like this

Main_id            field_id      primary_id          data      row_id
3314            326                3314                   cal      1
3314            325                3314                 antonio      1
3314            326                3314                atlanta      2
3314            308                3314                  ohio      2
3314            326                3314                    67      3



Main_id      primary_id      field1      field2      field3      
3314      3314              cal      antonio      
3314      3314             atlanta            ohio
3314      3314                67            



I want the data in table 1 to be populated in table 2 based on the below logic

1) Here There are three distinct row_id's . So 3 rows should be inserted in table 2
2) In table1 the field_id's 308,325,326(rows) are  converted to columns in table2
   field id 326 valued are inserted in field1 , 325 in field2 and 308 in field 3 respectively
   
so the value 'cal' in 1 row in data column of table1 has row_id 1 , so in table 2 it should populate in 1st row in field1 because
its field id was 326

so the value 'atlanta' in 3rd  row in data column of table1  has row_id 2 , so in table 2 it should populate in 2nd row in field1 because
its field id was 326


so the value 'ohio' in 4th row in data column of table1  has row_id 2 , so in table 2 it should populate in 2nd row in field3 because
its field id is 308 in table1
 

I HAVE implemented the above logic in 3 update statements , due to the large data the performance  is slow .


Can this be done in 1 or 2 statements

Thanks,
0
Comment
Question by:vijay11
  • 4
  • 3
7 Comments
 
LVL 27

Expert Comment

by:wilcoxon
ID: 35101969
These should be update statements (as you state) or insert statements?

I think this will work...  The syntax may be off as I can't test it right now but I think the logic is sound...
update table2 set field1 = t1.data, field2 = t2.data, field3 = t3.data
from table2 t, table1 t1, table1 t2, table1 t3
where t.main_id *= t1.main_id and t.primary_id *= t1.primary_id
and t1.row_id = 1 and t1.field_id = 308
and t.main_id *= t2.main_id and t.primary_id *= t2.primary_id
and t2.row_id = 2 and t2.field_id = 325
and t.main_id *= t3.main_id and t.primary_id *= t3.primary_id
and t3.row_id = 3 and t3.field_id = 326

Open in new window

0
 
LVL 27

Expert Comment

by:wilcoxon
ID: 35101982
Hmm.  On second thought, my update above won't work.  There is no data in table2 to differentiate between the rows other than the 3 values from table1 that are being updated.

Are there really no key fields in table2 to differentiate the rows?
0
 

Author Comment

by:vijay11
ID: 35102548
Any statement is fine .insert is more better than update
I prefer insert

Thanks
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:vijay11
ID: 35102694
Can you please post the select statement to insert to
Table 2
0
 
LVL 27

Accepted Solution

by:
wilcoxon earned 1500 total points
ID: 35103139
This should work (unless I made a typo or forgot the correct outer join syntax)...
insert table2
select t1.main_id, t1.primary_id, t1.data, t2.data, t3.data
from table1 t1, table1 t2, table1 t3
where t1.field_id = 308
and t1.main_id *= t2.main_id and t1.primary_id *= t2.primary_id
and t1.row_id *= t2.row_id 2 and t2.field_id = 325
and t1.main_id *= t3.main_id and t1.primary_id *= t3.primary_id
and t1.row_id *= t3.row_id and t3.field_id = 326
union
select t5.main_id, t5.primary_id, null, t5.data, t6.data
from table1 t5, table1 t6
where t5.field_id = 325
and t5.main_id *= t6.main_id and t5.primary_id *= t6.primary_id
and t5.row_id *= t6.row_id and t6.field_id = 326
and not exists (select * from table1 t4
	where t4.main_id = t5.main_id and t4.primary_id = t5.primary_id
	and t4.row_id = t5.row_id)
union
select t9.main_id, t9.primary_id, null, null, t9.data
from table1 t9
where t9.field_id = 326
and not exists (select * from table1 t7
	where t7.main_id = t9.main_id and t7.primary_id = t9.primary_id
	and t7.row_id = t9.row_id)
and not exists (select * from table1 t8
	where t8.main_id = t9.main_id and t8.primary_id = t9.primary_id
	and t8.row_id = t9.row_id)

Open in new window

0
 

Author Closing Comment

by:vijay11
ID: 35129490
I wanted the Colution in a single select statement
0
 
LVL 27

Expert Comment

by:wilcoxon
ID: 35130206
Yes but that is the best I could come up with (I'm fine with the B since I was not able to provide exactly what you wanted).

Ironically, doing updates would actually be cleaner in this case than inserts if table2 had row_id (without it, updates are even messier than inserts).  Although the simplest way of writing the updates would still require 3 update statements.

The problem with concisely handling this is that none of the three fields are guaranteed to exist.  I just thought of a "hack" that would likely be more efficient but still requires multiple statements (basically create a row that will always exist in table1 to anchor the query on).
insert table1
select distinct Main_id, 999, primary_id, 'dummy', row_id from table1

insert table2
select t1.main_id, t1.primary_id, t1.data, t2.data, t3.data
from table1 t0, table1 t1, table1 t2, table1 t3
where t0.field_id = 999
and t0.main_id *= t1.main_id and t0.primary_id *= t1.primary_id
and t0.row_id *= t1.row_id and t1.field_id = 308
and t0.main_id *= t2.main_id and t0.primary_id *= t2.primary_id
and t0.row_id *= t2.row_id and t2.field_id = 325
and t0.main_id *= t3.main_id and t0.primary_id *= t3.primary_id
and t0.row_id *= t3.row_id and t3.field_id = 326

delete table1 where field_id = 999

Open in new window

0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question