asked on

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,

wilcoxon

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

wilcoxon

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?

vijay11

ASKER

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

Thanks

vijay11

ASKER

Can you please post the select statement to insert to
Table 2

ASKER CERTIFIED SOLUTION

wilcoxon

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

vijay11

ASKER

I wanted the Colution in a single select statement

wilcoxon

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