# update a table by copying existing rows and change one field

**URL:** <https://forum.servoy.com/t/update-a-table-by-copying-existing-rows-and-change-one-field/18991>\
**Category:** Classic Servoy\
**Created:** [September 24, 2016, 11:29am UTC](https://forum.servoy.com/t/update-a-table-by-copying-existing-rows-and-change-one-field/18991 "2016-09-24T11:29:35Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![bobm](https://avatars.discourse-cdn.com/v4/letter/b/9fc348/32.png) [@bobm](https://forum.servoy.com/u/bobm)\
**Post date:** [September 24, 2016, 11:29am UTC](https://forum.servoy.com/t/update-a-table-by-copying-existing-rows-and-change-one-field/18991/1 "2016-09-24T11:29:35Z")

</div>

I am trying to update a table by copying existing rows and change one field in the new row. I was trying with SQL through pgadmin, but running into primary key issues.  
The table has sequential primary keys and getting the following error:

ERROR: null value in column “diagnosis\_procedure\_id” violates not-null constraint

I’m thinking it might be better to do this through Servoy, but not sure how to accomplish this for all rows that contain the current diagnosis\_id

The SQL I’m using in pgadmin is:

```auto
INSERT INTO diagnosis_procedures 
      (diagnosis_procedure_id, flag_mutially_exclusive, flag_not_appropriate, region_char_code, procedure_id, hide, code_type, diagnosis_id)

SELECT , flag_mutially_exclusive, flag_not_appropriate, region_char_code, procedure_id, hide, code_type, 14733

FROM diagnosis_procedures

WHERE diagnosis_id = 13781

```

Ultimately I would like to have input boxes to enter the current diagnosis\_id and the new diagnosis\_id

Thank you in advance for any help.  
Bob

---

<div class="post-metadata">

**Author:** ![mboegem](https://yyz2.discourse-cdn.com/flex010/user_avatar/forum.servoy.com/mboegem/32/4399_2.png) [@mboegem](https://forum.servoy.com/u/mboegem)\
**Post date:** [September 24, 2016, 5:11pm UTC](https://forum.servoy.com/t/update-a-table-by-copying-existing-rows-and-change-one-field/18991/2 "2016-09-24T17:11:06Z")

</div>

Hi Bob,

in order to do this, the best way would be to use a PostgreSQL sequence  
[https://www.postgresql.org/docs/8.1/sta … uence.html](https://www.postgresql.org/docs/8.1/static/sql-createsequence.html)

This way PostgreSQL will handle the unique values for you, and if you like it’s possible to use these sequences with regular Servoy foundsets as well.

PostgreSQL sequences have a couple of functions that can be used as documented here:  
[https://www.postgresql.org/docs/9.1/sta … uence.html](https://www.postgresql.org/docs/9.1/static/functions-sequence.html)

Let’s say you’ve created a sequence with name ‘pk\_seq\_diagnosis\_procedures’  
The insert statement you’ve given would be:

```auto
INSERT INTO diagnosis_procedures 
      (diagnosis_procedure_id, flag_mutially_exclusive, flag_not_appropriate, region_char_code, procedure_id, hide, code_type, diagnosis_id)

SELECT nextval('pk_seq_diagnosis_procedures'), flag_mutially_exclusive, flag_not_appropriate, region_char_code, procedure_id, hide, code_type, 14733

```

Although all of this is perfectly possible using the rawSQL plugin from within Servoy, please be aware that any Servoy client running at the time of executing this statement, won’t receive any broadcast event of the insert and therefor doesn’t know about the existence of the new record(s). (There’s some exception when foundset based on the table has not been cached yet, but generally you shouldn’t count on that)  
This could be resolved by using the plugin.rawSQL.flushClientCache() function, but it’s an expensive operation not worth when only inserting single rows.  
Please also note in case you use a servoy-sequence on the PK of the diagnosis\_procedures table, this won’t be updated which will result in a violation of unique value whenever you insert records ‘the Servoy way’

I recently had to do the exact same thing in an application, but handling 10.000 records at a time which took Servoy almost 3 minutes to insert and could be done using rawSQL within 1 second!  
I used a database sequence for this and have Servoy use this as well, so there will never be a violation of unique value.

The Servoy way would be something like having to foundsets, 1 source, 1 destination.  
On the source foundset, perform a find/search operation.  
Iterate over the records you’ve found and create records in the destination foundset, while setting the dataproviders with the correct values.

Having written all of the above:  
I’d say keep things simple and use the power of Servoy to accomplish what you want, unless you really need to handle high volume data.  
Although doing it the Servoy way will stall your development at first, but you will gain speed in building your apps/code once your Servoy skills improve.  
Also using rawSQL and mix this with the way Servoy works and behaves is something you shouldn’t start with.  
Only do this when you have experience in Servoy and fully understand how Servoy handles foundsets, pk, sequences, etc., etc.

Hope this helps.

---

<div class="post-metadata">

**Author:** ![bobm](https://avatars.discourse-cdn.com/v4/letter/b/9fc348/32.png) [@bobm](https://forum.servoy.com/u/bobm)\
**Post date:** [September 25, 2016, 4:31pm UTC](https://forum.servoy.com/t/update-a-table-by-copying-existing-rows-and-change-one-field/18991/3 "2016-09-25T16:31:43Z")

</div>

Marc,  
Thank you for your reply - this sounds like exactly what I need. But, I’m getting the following error:

ERROR: syntax error at or near “SEQUENCE”  
LINE 1: …E off, VERBOSE off, COSTS on, BUFFERS off )CREATE SEQUENCE p…  
\*\*\*\*\*\*\*\*\*\* Error \*\*\*\*\*\*\*\*\*\*  
ERROR: syntax error at or near “SEQUENCE”  
SQL state: 42601  
Character: 66

```auto
CREATE SEQUENCE pk_seq_diag_proc START WITH 1610447 INCREMENT By 1; 

INSERT INTO diagnosis_procedures 
      (diagnosis_procedure_id, flag_mutially_exclusive, flag_not_appropriate, region_char_code, procedure_id, hide, code_type, diagnosis_id)

SELECT nextval(pk_seq_diag_proc), flag_mutially_exclusive, flag_not_appropriate, region_char_code, procedure_id, hide, code_type, 14733

FROM diagnosis_procedures

WHERE diagnosis_id = 13781

```

Any idea why?  
Thanks again,  
Bob

---

<div class="post-metadata">

**Author:** ![mboegem](https://yyz2.discourse-cdn.com/flex010/user_avatar/forum.servoy.com/mboegem/32/4399_2.png) [@mboegem](https://forum.servoy.com/u/mboegem)\
**Post date:** [September 25, 2016, 4:58pm UTC](https://forum.servoy.com/t/update-a-table-by-copying-existing-rows-and-change-one-field/18991/4 "2016-09-25T16:58:39Z")

</div>

Hi Bob,

can’t tell what’s wrong from what you’ve posted here.  
My PG db doesn’t complain on the exact same statement. (v.9.4)

What version of PG are you running? ( SELECT version() )  
I also installed a separate version of PG, using the EnterpriseDB distibution ([http://www.enterprisedb.com](http://www.enterprisedb.com))

---

<div class="post-metadata">

**Author:** ![bobm](https://avatars.discourse-cdn.com/v4/letter/b/9fc348/32.png) [@bobm](https://forum.servoy.com/u/bobm)\
**Post date:** [September 25, 2016, 5:20pm UTC](https://forum.servoy.com/t/update-a-table-by-copying-existing-rows-and-change-one-field/18991/5 "2016-09-25T17:20:16Z")

</div>

Marc,

I’m running version 9.1.3 I will try a newer version.

Thanks again,  
Bob

---

<div class="post-metadata">

**Author:** ![bobm](https://avatars.discourse-cdn.com/v4/letter/b/9fc348/32.png) [@bobm](https://forum.servoy.com/u/bobm)\
**Post date:** [September 25, 2016, 6:24pm UTC](https://forum.servoy.com/t/update-a-table-by-copying-existing-rows-and-change-one-field/18991/6 "2016-09-25T18:24:35Z")

</div>

Marc,  
I have loaded PG v 9.4 and was getting the same error. I changed the name of the SEQUENCE “pk\_seq\_diag\_proc” to be the actual column name “diagnosis\_procedure\_id” so my query now looks like this:

```auto
CREATE SEQUENCE diagnosis_procedure_id START WITH 1610447 INCREMENT By 1;

INSERT INTO diagnosis_procedures 
      (diagnosis_procedure_id, flag_mutially_exclusive, flag_not_appropriate, region_char_code, procedure_id, hide, code_type, diagnosis_id)

SELECT nextval(diagnosis_procedure_id), flag_mutially_exclusive, flag_not_appropriate, region_char_code, procedure_id, hide, code_type, 14733

FROM diagnosis_procedures

WHERE diagnosis_id = 13781;

```

But now getting the following:

[QUERY] CREATE SEQUENCE diagnosis\_procedure\_id START WITH 1610447 INCREMENT By 1  
[WARNING] INSERT INTO diagnosis\_procedures  
(diagnosis\_procedure\_id, flag\_mutially\_exclusive, flag\_not\_appropriate, region\_char\_code, procedure\_id, hide, code\_type, diagnosis\_id)  
SELECT nextval(diagnosis\_procedure\_id), flag\_mutially\_exclusive, flag\_not\_appropriate, region\_char\_code, procedure\_id, hide, code\_type, 14733  
FROM diagnosis\_procedures  
WHERE diagnosis\_id = 13781  
ERROR: could not open relation with OID 1058304

The same is happening in version 9.1

Thanks,  
Bob

---

<div class="post-metadata">

**Author:** ![mboegem](https://yyz2.discourse-cdn.com/flex010/user_avatar/forum.servoy.com/mboegem/32/4399_2.png) [@mboegem](https://forum.servoy.com/u/mboegem)\
**Post date:** [September 25, 2016, 9:16pm UTC](https://forum.servoy.com/t/update-a-table-by-copying-existing-rows-and-change-one-field/18991/7 "2016-09-25T21:16:01Z")

</div>

Hi Bob,

try executing SELECT nextval(diagnosis\_procedure\_id) with quotes around the sequence name, so:

```auto
SELECT nextval('diagnosis_procedure_id')

```

---

<div class="post-metadata">

**Author:** ![bobm](https://avatars.discourse-cdn.com/v4/letter/b/9fc348/32.png) [@bobm](https://forum.servoy.com/u/bobm)\
**Post date:** [September 26, 2016, 12:13pm UTC](https://forum.servoy.com/t/update-a-table-by-copying-existing-rows-and-change-one-field/18991/8 "2016-09-26T12:13:48Z")

</div>

Thank you Marc! That did it!

I do appreciate all your help

Thanks again,  
Bob