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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.


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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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.

777 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