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

Question by:vijay11
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
LVL 26

Expert Comment

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 =, field2 =, field3 =
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

LVL 26

Expert Comment

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?

Author Comment

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

Increase Agility with Enabled Toolchains

Connect your existing build, deployment, management, monitoring, and collaboration platforms. From Puppet to Chef, HipChat to Slack, ServiceNow to JIRA, Splunk to New Relic and beyond, hand off data between systems to engage the right people.

Connect with xMatters.


Author Comment

ID: 35102694
Can you please post the select statement to insert to
Table 2
LVL 26

Accepted Solution

wilcoxon earned 500 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,,,
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
select t5.main_id, t5.primary_id, null,,
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)
select t9.main_id, t9.primary_id, null, null,
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


Author Closing Comment

ID: 35129490
I wanted the Colution in a single select statement
LVL 26

Expert Comment

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,,,
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


Featured Post

Turn Insights into Action

Communication across every corner of your business is essential to increase the velocity of your application delivery and support pipeline. Automate, standardize, and contextualize your communication processes with xMatters.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
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 setup several different housekeeping processes for a SQL Server.

691 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