Oledb Update query

Help Studio
, , ,
1

I have an excel file with multiple duplicate rows with no unique identifer. I am connecting that excel file with oledb and want to update only one single row out of many matching rows. How to do that

2

Hi @Pinky_AG

Check below for your reference

This is from a column, but you have to give your columns so that it will get unqiue values row only

Hope this may help you

Thanks,
Srini

3

Hello @Srini84 ,

Thank you for the quick reply…

However my case 2 row is exactly duplicate without any uniqe column value… and i want to update only one row using oledb update query

Any thought

4

Hi @Pinky_AG

Check as below

image
image941×351 33.3 KB

I have given as below

dt_Test.DefaultView.ToTable(True, "Column1", "Column2")

Hope this helps you

Thanks,
Srini

5

Thank you…

I have the data in the excel file not in datatable and i am connecting that excel with oledb connection and run oledb update query

6

Hi @Pinky_AG

You can use Run Query and write Select * from your table, those will get all your data into Datatable then you can use update query after placing the filter as above

Hope this may help you

Thanks,
Srini

7

Correct but the only limitation i have is i need to update the excel file using oledb connector only

8

@Pinky_AG

I believe this is the only way to do manipulation and then update query

You use Run Query and then Bulk update actiivty

Thanks,
Srini

9

@Pinky_AG

Once you use Oledb for Excel then it becomes as Excel as Database, so you can use Select query and then update the same after manipulation

Thanks,
Srini

10

Select is not an issue, i am able to get one row using TOP keyword but update quary is not working …can you help me with update query which will update one row of excel file

11

@Pinky_AG

Use Run Command activity and write as below

UPDATE [Sheet1$] SET Column3 = ‘NewValue3’ WHERE Column1 = ‘Value1’ AND Column2 = ‘Value2’

and you use above as per your columns

Thanks,
Srini

12

@Srini84

If i run the following update command all the dupliacte row will be updated at once(as i have no unique identifier) which i dont want. I want to update only first row for the condition
WHERE Column1 = ‘Value1’ AND Column2 = ‘Value2’

13

You can perform an update on a subselect using TOP and your condition in it:

UPDATE (SELECT TOP 1 * FROM [Sheet1$] WHERE Column1 = ‘Value1’ AND Column2 = ‘Value2’) Set Column3=‘NewValue’

The result then looks like this:
image

14

@BotDave

Thank you .
This is exactly what I was looking.
It is working .

15

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.