# Trouble updating table in PostgreSQL

**URL:** <https://forum.xojo.com/t/trouble-updating-table-in-postgresql/63078>\
**Category:** Databases\
**Created:** [April 30, 2021, 10:17pm UTC](https://forum.xojo.com/t/trouble-updating-table-in-postgresql/63078 "2021-04-30T22:17:40Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![Tim\_Seyfarth](https://forum.xojo.com/letter_avatar_proxy/v4/letter/t/d26b3c/32.png) [@Tim\_Seyfarth](https://forum.xojo.com/u/Tim_Seyfarth)\
**Post date:** [April 30, 2021, 10:17pm UTC](https://forum.xojo.com/t/trouble-updating-table-in-postgresql/63078/1 "2021-04-30T22:17:40Z")

</div>

Hello all,

I’m trying to update records in a single table, one at a time. I first do a get, then read in the column data.

I alter certain columns as necessary then update the record.

This is the SQL Command  
UPDATE transaction\_sms SET rowid=31,created\_sql\_date\_time=‘2021-04-30 15:08:20’,sent\_sql\_date\_time=‘2021-04-30 15:11:48’,confirmed\_sql\_date\_time=‘2021-04-30 15:11:48’,msg\_sent\_to=’ ‘,msg=‘No Unit Assigned Alarm Not Warranted’,gate\_code=0,tenant\_name=’ ‘,tenant\_account=0,unitid=‘NA’,from\_ph\_num=’+12182929777’,to\_ph\_num=’’,addr=200,ch=4,msgtext=‘This message is from the Axcys System Controller at . It is to inform you of a recent entry/exit/door event at your storage facility. No Phone Number set up for this unit or account’;

The primary key is the rowid.  
If I leave the command as is shown, it fails saying a duplicate key - eventhough I am trying to so an UPDATE and not an INSERT.

If I leave the rowid out, then it updates ALL records!

What am I doing wrong?  
Thanks,  
Tim

---

<div class="post-metadata">

**Author:** ![Wayne\_Golding](https://forum.xojo.com/user_avatar/forum.xojo.com/wayne_golding/32/165_2.png) [@Wayne\_Golding](https://forum.xojo.com/u/Wayne_Golding)\
**Post date:** [April 30, 2021, 10:32pm UTC](https://forum.xojo.com/t/trouble-updating-table-in-postgresql/63078/2 "2021-04-30T22:32:03Z")

</div>

You need to add a WHERE clause to update only that row as below.

UPDATE transaction\_sms SET created\_sql\_date\_time=‘2021-04-30 15:08:20’,sent\_sql\_date\_time=‘2021-04-30 15:11:48’,confirmed\_sql\_date\_time=‘2021-04-30 15:11:48’,msg\_sent\_to=’ ‘,msg=‘No Unit Assigned Alarm Not Warranted’,gate\_code=0,tenant\_name=’ ‘,tenant\_account=0,unitid=‘NA’,from\_ph\_num=’+12182929777’,to\_ph\_num=’’,addr=200,ch=4,msgtext=‘This message is from the Axcys System Controller at . It is to inform you of a recent entry/exit/door event at your storage facility. No Phone Number set up for this unit or account’ WHERE rowid=31;

---

<div class="post-metadata">

**Author:** ![Tim\_Seyfarth](https://forum.xojo.com/letter_avatar_proxy/v4/letter/t/d26b3c/32.png) [@Tim\_Seyfarth](https://forum.xojo.com/u/Tim_Seyfarth)\
**Post date:** [April 30, 2021, 10:39pm UTC](https://forum.xojo.com/t/trouble-updating-table-in-postgresql/63078/3 "2021-04-30T22:39:50Z")

</div>

Thank you Wayne!  
I’ll try it out.  
Thank you

---

<div class="post-metadata">

**Author:** ![Kevin\_Bryson](https://forum.xojo.com/letter_avatar_proxy/v4/letter/k/a587f6/32.png) [@Kevin\_Bryson](https://forum.xojo.com/u/Kevin_Bryson)\
**Post date:** [May 6, 2021, 4:04am UTC](https://forum.xojo.com/t/trouble-updating-table-in-postgresql/63078/4 "2021-05-06T04:04:51Z")

</div>

There is an implied WHERE TRUE if you leave the WHERE clause out, and it is actually updating all of the records again, but it fails on the first row\_id that isn’t 31, because there is already a 31. Wayne’s code looks great!