How to execute subQuery using oledb connection

[syed]

Hi,

I want to execute below like query using oledb (referring two different excel)

UPDATE [Onboarded resources$] a SET End_Date = (SELECT End_Date FROM [Sheet1$] b where b.POP_Notes_ID=a.Notes_ID);

How can a

 

[sivagelli]

Hi,

I want to execute below like query using oledb (referring two different excel)

UPDATE [Onboarded resources$] a SET End_Date = (SELECT End_Date FROM [Sheet1$] b where b.POP_Notes_ID=a.Notes_ID);

How can a

Is it two different excel worksheets in the same workbook
OR
entirely different workbooks?

If it is same workbook, you can execute the query, however, if it is from different workbooks you cannot.

 

[syed]

it is from different workbook.

 

[syed]

So, I need to put both sheet in one excel and need to execute the code right? And also please confirm Syntax in the Query is correct or not

 

[syed]

Is it two different excel worksheets in the same workbook
OR
entirely different workbooks?

If it is same workbook, you can execute the query, however, if it is from different workbooks you cannot.

Hi,

Now I have two sheets in one excel and executing below query

update [Onboarded resources$] a SET End_Date = (SELECT End_Date FROM [Sheet1$] b where b.POP_Notes_ID=a.Notes_ID);

but still it is throwing error like.. one or more parameters required. Anything wrong with the query?


@VJR kindly provide your comment too.

 

[sivagelli]

Syntactically, sql is not correct. You may try something like this-

UPDATE t
SET t.col1 = o.col1
FROM
other_table o
JOIN
t ON t.id = o.id
Where <condition>

This thread might help! This thread is for SQL Server, but don't know if this works for Excel workbook. It will be an interesting exercise. Good luck!

 

[udayasree]

Hi,
Can anyone help me with the Update query in OLEDB
I have columns named Material ,PF in sheet1 and columns named Material and Center in sheet2. I have to update PF in sheet1 by comparing Material values in two sheets and pulling the Center value for whichever the Material value matches..I have to update PF value in sheet1 with Center Value in sheet2 by comparing Materials.

 

[sahil_raina_91]

Hi,
Can anyone help me with the Update query in OLEDB
I have columns named Material ,PF in sheet1 and columns named Material and Center in sheet2. I have to update PF in sheet1 by comparing Material values in two sheets and pulling the Center value for whichever the Material value matches..I have to update PF value in sheet1 with Center Value in sheet2 by comparing Materials.

Do you have an option to skip using OLEDB?
You can achieve the same via VLOOKUP formula in excel itself (without needing to use OLEDB). Paste below formula in Sheet1 B2 cell
=VLOOKUP(A2,Sheet2!$A$2:$B$12,2,FALSE)

A2 : Assuming A1=Material, A2 is the first cell containing data
Sheet2!$A$2:$B$12 : Range of all data in sheet2 (B12 is the last row in sheet 2)

 

[udayasree]

Do you have an option to skip using OLEDB?
You can achieve the same via VLOOKUP formula in excel itself (without needing to use OLEDB). Paste below formula in Sheet1 B2 cell
=VLOOKUP(A2,Sheet2!$A$2:$B$12,2,FALSE)

A2 : Assuming A1=Material, A2 is the first cell containing data
Sheet2!$A$2:$B$12 : Range of all data in sheet2 (B12 is the last row in sheet 2)

Thank you for the solution.

But I have few more columns beside PF(ColB) in Sheet1 named OriginGrp(ColC), MatValue(ColD), ProductFamily(ColE) and Sheet2 also have OriginGrp(ColC), MatValue(ColD), ProductFamily(ColE). So similarly by comparing Material Value in both sheets, I have to fill these 3 cols of sheet1 with 3 respective cols of sheet2.
And for your information, Column A is Material in both the sheets
What should be the formula for each of this??

Thanks in Advance..

 

[sahil_raina_91]

Thank you for the solution.

But I have few more columns beside PF(ColB) in Sheet1 named OriginGrp(ColC), MatValue(ColD), ProductFamily(ColE) and Sheet2 also have OriginGrp(ColC), MatValue(ColD), ProductFamily(ColE). So similarly by comparing Material Value in both sheets, I have to fill these 3 cols of sheet1 with 3 respective cols of sheet2.
And for your information, Column A is Material in both the sheets
What should be the formula for each of this??

Thanks in Advance..

Write this formula is Sheet1 B2 cell
=VLOOKUP(A2,Sheet2!$A$2:$E$12,{2,3,4,5},FALSE)

 

[udayasree]

Write this formula is Sheet1 B2 cell
=VLOOKUP(A2,Sheet2!$A$2:$E$12,{2,3,4,5},FALSE)

Thank you @sahil_raina_91 for the solution..

I need one more help

I have PLANT in Col B, REVAL IN LC in Col V, REVAL USD in Col W.
Now I want to calculate REVAL USD for all PLANTs.

For Plants 5100,5120,5400,5420,5619, REVAL USD= REVAL IN LC / 4-----(REVAL IN LC divided by 4)
For all other Plants ,REVAL USD= REVAL IN LC

For your information, I have many plants other than 5100,5120,5400,5420,5619 in the PLANTS (Col B)

What is the VLOOKUP formula for this scenario?

Thanks in Advance

 

[sahil_raina_91]

Thank you @sahil_raina_91 for the solution..

I need one more help

I have PLANT in Col B, REVAL IN LC in Col V, REVAL USD in Col W.
Now I want to calculate REVAL USD for all PLANTs.

For Plants 5100,5120,5400,5420,5619, REVAL USD= REVAL IN LC / 4-----(REVAL IN LC divided by 4)
For all other Plants ,REVAL USD= REVAL IN LC

For your information, I have many plants other than 5100,5120,5400,5420,5619 in the PLANTS (Col B)

What is the VLOOKUP formula for this scenario?

Thanks in Advance

=IF(OR(B2=5100,B2=5120,B2=5400,B2=5420,B2=5619),V2/4,V2)