Query using multiple columns in IN Clause
-
Hi Everyone, I am having one table which contains 5 columns. These five columns together contitute the primary key. I am having one more table which contains data for these primary key. I need to write a query which will bring the data from the second table for those primary keys which are available in first table. :doh: :doh: :doh: :doh:
CodeManiac xxxxxxxxxx xxxxxxxxxx
If I understand you correctly, you just need a simple inner join.
Select ...
From YourPrimaryTable
InnerJoin YourDetailTable
on YourPrimaryTable.Column1 = YourDetailTable.Column1
and YourPrimaryTable.Column2 = YourDetailTable.Column2
...Hope that helps. Syed Mehroz Alam
-
If I understand you correctly, you just need a simple inner join.
Select ...
From YourPrimaryTable
InnerJoin YourDetailTable
on YourPrimaryTable.Column1 = YourDetailTable.Column1
and YourPrimaryTable.Column2 = YourDetailTable.Column2
...Hope that helps. Syed Mehroz Alam
Thanks for your reply. :-D :-D Can we do the same thing without taking join. I mean can we use IN clause for multiple columns?
CodeManiac xxxxxxxxxx xxxxxxxxxx
-
Thanks for your reply. :-D :-D Can we do the same thing without taking join. I mean can we use IN clause for multiple columns?
CodeManiac xxxxxxxxxx xxxxxxxxxx
Confusing...I wonder what do you need. Can you explain using a simple example.
-
Hi Everyone, I am having one table which contains 5 columns. These five columns together contitute the primary key. I am having one more table which contains data for these primary key. I need to write a query which will bring the data from the second table for those primary keys which are available in first table. :doh: :doh: :doh: :doh:
CodeManiac xxxxxxxxxx xxxxxxxxxx
-
Confusing...I wonder what do you need. Can you explain using a simple example.
Sorry to confuse you. Let me explain you complete scenario. I am having two databases DB1 and DB2. DB1 has single table T1 and 15 columns namely C1, C2, C3 etc. DB2 has two table T1 and T2. T1 table is identical in both the databases. So T1 table has same 15 columns and same data in both the databases. T2 has 5 columns C1, C2, C3, C4,C5 which together constitute primary key. In T1 table of DB1 database, some of the records are modified. T2 table of database DB2 has the primary keys of all the modified records. I need to update table T1 of database DB2 for the modified records. I need a way to accomplish this. Hopefully I am clear now.
CodeManiac xxxxxxxxxx xxxxxxxxxx
-
Sorry to confuse you. Let me explain you complete scenario. I am having two databases DB1 and DB2. DB1 has single table T1 and 15 columns namely C1, C2, C3 etc. DB2 has two table T1 and T2. T1 table is identical in both the databases. So T1 table has same 15 columns and same data in both the databases. T2 has 5 columns C1, C2, C3, C4,C5 which together constitute primary key. In T1 table of DB1 database, some of the records are modified. T2 table of database DB2 has the primary keys of all the modified records. I need to update table T1 of database DB2 for the modified records. I need a way to accomplish this. Hopefully I am clear now.
CodeManiac xxxxxxxxxx xxxxxxxxxx
I got it now. This seems a difficult issue. I suggest you to create some trigger on db1.T1 that updates the modified rows in db2. If you still want to use some batch update, the most inappropriate way will be to use a cursor loop. But wait, lets hope someone else in the forum finds a more efficient way. Good luck. Syed Mehroz Alam
-
Sorry to confuse you. Let me explain you complete scenario. I am having two databases DB1 and DB2. DB1 has single table T1 and 15 columns namely C1, C2, C3 etc. DB2 has two table T1 and T2. T1 table is identical in both the databases. So T1 table has same 15 columns and same data in both the databases. T2 has 5 columns C1, C2, C3, C4,C5 which together constitute primary key. In T1 table of DB1 database, some of the records are modified. T2 table of database DB2 has the primary keys of all the modified records. I need to update table T1 of database DB2 for the modified records. I need a way to accomplish this. Hopefully I am clear now.
CodeManiac xxxxxxxxxx xxxxxxxxxx
You have to create any trigger or you can make changes on T1 of DB2 something like query down below which you execute that both querys at same time:
Update DB1.dbo.T1 set DB1.dbo.T1.column='value' where DB1.dbo.T1.colname ='conditionvaue' Update DB2.dbo.T1 set DB2.dbo.T1.column='value' where DB2.dbo.T1.colname ='conditionvaue'
I Love T-SQL "Don't torture yourself,let the life to do it for you."
-
Sorry to confuse you. Let me explain you complete scenario. I am having two databases DB1 and DB2. DB1 has single table T1 and 15 columns namely C1, C2, C3 etc. DB2 has two table T1 and T2. T1 table is identical in both the databases. So T1 table has same 15 columns and same data in both the databases. T2 has 5 columns C1, C2, C3, C4,C5 which together constitute primary key. In T1 table of DB1 database, some of the records are modified. T2 table of database DB2 has the primary keys of all the modified records. I need to update table T1 of database DB2 for the modified records. I need a way to accomplish this. Hopefully I am clear now.
CodeManiac xxxxxxxxxx xxxxxxxxxx
Perhaps an inner join in the update?
UPDATE T1
SET ...
FROM T1
INNER JOIN T2
ON T1.blah=T2.blah
AND ... -
Perhaps an inner join in the update?
UPDATE T1
SET ...
FROM T1
INNER JOIN T2
ON T1.blah=T2.blah
AND ...Just one more thing that this process will happen in Batch.
CodeManiac xxxxxxxxxx xxxxxxxxxx
-
Just one more thing that this process will happen in Batch.
CodeManiac xxxxxxxxxx xxxxxxxxxx
What does that mean?
-
What does that mean?
I mean the proces will happen every night for the data that changed during day time.
CodeManiac xxxxxxxxxx xxxxxxxxxx
-
I mean the proces will happen every night for the data that changed during day time.
CodeManiac xxxxxxxxxx xxxxxxxxxx
Shouldn't be a problem then. I always try to have the database engine do most of the work.