SYbase

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,
vijay11Asked:
Who is Participating?
 
wilcoxonConnect With a Mentor Commented:
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
 
wilcoxonCommented:
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
 
wilcoxonCommented:
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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
vijay11Author Commented:
Any statement is fine .insert is more better than update
I prefer insert

Thanks
0
 
vijay11Author Commented:
Can you please post the select statement to insert to
Table 2
0
 
vijay11Author Commented:
I wanted the Colution in a single select statement
0
 
wilcoxonCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.