1. Christian Breu
  2. as Valentina DB Server
  3. Wednesday, March 27 2019, 11:08 PM
  4.  Subscribe via email
[How] is it possible to just update existing records while using vCursor.importText()?
Comment
There are no comments made yet.
Christian Breu Accepted Answer
Is it possible or not?
Comment
There are no comments made yet.
  1. more than a month ago
  2. as Valentina DB Server
  3. # 1
Sergey Pashkov Accepted Answer
Hello Christian,

For example, if the imported text contains the RecID or primary key column, right?
Comment
There are no comments made yet.
  1. more than a month ago
  2. as Valentina DB Server
  3. # 2
Christian Breu Accepted Answer
Hi Sergey

Actually not really but they are products and have a barcode-number.
Is it possible directly while importing from text?
Comment
There are no comments made yet.
  1. more than a month ago
  2. as Valentina DB Server
  3. # 3
Sergey Pashkov Accepted Answer
Hi Christian,

No, it is impossible to do directly.

One of the possible ways:
1. Create intermediate (temporary or even RAM) table
2. Import records to this table
3. Use MERGE command to update existing and insert new records in the target table
References
  1. http://valentina-db.com/docs/dokuwiki/v9/doku.php?id=valentina:vcomponents:vsql:reference:merge
Comment
There are no comments made yet.
  1. more than a month ago
  2. as Valentina DB Server
  3. # 4
Christian Breu Accepted Answer
Hi Sergey

Thank you for the clear answer.
I will take a look at that.


Best regards.

Chris.
Comment
There are no comments made yet.
  1. more than a month ago
  2. as Valentina DB Server
  3. # 5
Christian Breu Accepted Answer
Hello Sergey

Have to reactivate this thread...
Trying to use Merge but it does not work as I am expecting. :(

The sample from Wiki does not work – using dot-notation for fields is throwing errors!?
Found a sample from Ruslan that works but it is too simple.


this works without throwing an error:

MERGE main as mtarget
USING imp as isource ON (isource.artikelnummer = mtarget.artikelnummer)
WHEN MATCHED THEN
UPDATE SET name = isource.name


this is throwing an error:

MERGE main as mtarget
USING imp as isource ON (isource.artikelnummer = mtarget.artikelnummer AND isource.barcode = mtarget.barcode)
WHEN MATCHED THEN
UPDATE SET name = isource.name


this works without throwing an error:

MERGE main as mtarget
USING imp as isource ON (isource.artikelnummer = mtarget.artikelnummer AND isource.barcode <> mtarget.barcode)
WHEN MATCHED THEN
UPDATE SET name = isource.name



**********************************************************

I want to do anything like this:

MERGE main as mtarget
USING imp as isource ON (isource.artikelnummer = mtarget.artikelnummer AND isource.barcode = mtarget.barcode)
WHEN NOT MATCHED BY TARGET THEN
INSERT(artikelnummer, barcode, name) VALUES(isource.artikelnummer, isource.barcode, isource.name)
WHEN MATCHED THEN
UPDATE SET mtarget.name = isource.name


and a second one to delete:

MERGE main as mtarget
USING imp as isource
ON (isource.artikelnummer = mtarget.artikelnummer and isource.barcode = mtarget.barcode AND remove = 1)
WHEN MATCHED THEN
DELETE




What did I misunderstand?
How to make it work?


Best regards.
Chris
Comment
There are no comments made yet.
  1. more than a month ago
  2. as Valentina DB Server
  3. # 6
Christian Breu Accepted Answer
I tested with VServer 7.1.6 – is it a problem with that version?
Comment
There are no comments made yet.
  1. more than a month ago
  2. as Valentina DB Server
  3. # 7
Sergey Pashkov Accepted Answer
Hello Christian,

No, it is in the latest versions, too.
It is a limitation of the JOIN operator, we'll check if there any other way to do it.
Comment
There are no comments made yet.
  1. more than a month ago
  2. as Valentina DB Server
  3. # 8
Christian Breu Accepted Answer
Hi Sergey

A full implementation of Merge, to work as expected, would be great – save my day...
...and a reason for my customer to update to the latest version of VServer. ;)


Thank you for support.
Best regards.

Chris
Comment
There are no comments made yet.
  1. more than a month ago
  2. as Valentina DB Server
  3. # 9
Christian Breu Accepted Answer
Do I have to set it as a feature request somewhere?
Comment
There are no comments made yet.
  1. more than a month ago
  2. as Valentina DB Server
  3. # 10
Sergey Pashkov Accepted Answer
I added a feature request for JOIN operation which is used in MERGE command.
References
  1. http://valentina-db.com/bt/view.php?id=8513
Comment
There are no comments made yet.
  1. more than a month ago
  2. as Valentina DB Server
  3. # 11
Christian Breu Accepted Answer
Thank you Sergey!
Comment
There are no comments made yet.
  1. more than a month ago
  2. as Valentina DB Server
  3. # 12
Christian Breu Accepted Answer
Hoping for some progress about this topic.
Comment
There are no comments made yet.
  1. more than a month ago
  2. as Valentina DB Server
  3. # 13
Hi Christian,

Try with this link code :

ON (isource.artikelnummer = mtarget.artikelnummer AND isource.barcode = CONCAT(mtarget.barcode))
Comment
There are no comments made yet.
  1. more than a month ago
  2. as Valentina DB Server
  3. # 14
Ruslan Zasukhin Accepted Answer
Hi Christian,

Ivan worked the prev week to improve join, and MERGE

we will ship OR 9.1.5 or 9.2 next week.

IF this is VERY VERY urgent -- let me know, then we will try 9.1.5 asap
if can wait to weekend - then we hope for 9.2 ready
Comment
There are no comments made yet.
  1. more than a month ago
  2. as Valentina DB Server
  3. # 15
Christian Breu Accepted Answer
Hello Ruslan

Thank you for the info.
What does it mean v9.2 – before/until may 20?
This should be ok.
Comment
There are no comments made yet.
  1. more than a month ago
  2. as Valentina DB Server
  3. # 16
Ruslan Zasukhin Accepted Answer
9.1.5 archive can be found here already:

http://valentina-db.com/download/prev_releases/9.1.5
Comment
There are no comments made yet.
  1. more than a month ago
  2. as Valentina DB Server
  3. # 17
Christian Breu Accepted Answer
is it implemented already?
Comment
There are no comments made yet.
  1. more than a month ago
  2. as Valentina DB Server
  3. # 18
Christian Breu Accepted Answer
Can you please update the samples too?
I could not get it to work with 9.1.5
Comment
There are no comments made yet.
  1. more than a month ago
  2. as Valentina DB Server
  3. # 19
Ivan Smahin Accepted Answer
It should work for "MATCHED" actions as you wrote above ("NOT MATCHED" actions are still not supported for such a complex conditions).
Comment
There are no comments made yet.
  1. more than a month ago
  2. as Valentina DB Server
  3. # 20
  • Page :
  • 1
  • 2


There are no replies made for this post yet.
However, you are not allowed to reply to this post.

Categories

Announcements & News
  1. 0 subcategories
Valentina Studio
  1. 2 subcategories
Valentina Server
  1. 4 subcategories
Valentina Database ADK
  1. 0 subcategories
Valentina Reports ADK
  1. 0 subcategories
Other Discussions
  1. 0 subcategories
BETA Testing
  1. 0 subcategories
Education & Research
  1. 0 subcategories
Omegabundle
  1. 0 subcategories