Solved

SYbase

Posted on 2011-03-10
7
301 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 26

Expert Comment

by:wilcoxon
Comment Utility
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 26

Expert Comment

by:wilcoxon
Comment Utility
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
Comment Utility
Any statement is fine .insert is more better than update
I prefer insert

Thanks
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

Author Comment

by:vijay11
Comment Utility
Can you please post the select statement to insert to
Table 2
0
 
LVL 26

Accepted Solution

by:
wilcoxon earned 500 total points
Comment Utility
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
Comment Utility
I wanted the Colution in a single select statement
0
 
LVL 26

Expert Comment

by:wilcoxon
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

763 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now