Trouble updating table in PostgreSQL

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

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;

3 Likes

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

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!

Forum for Xojo Programming Language and IDE. Copyright © 2021 Xojo, Inc.