Trouble updating table in PostgreSQL

Databases
1

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

2

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
3

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

4

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!