Postgres Update table with limit

Question

Postgres fails when I use limit with update statement

I want to update just the first record of a table

UPDATE test1 SET name="user101" LIMIT 1;

this fails in postgres as I am using limit;

I can do this

UPDATE test1 SET name="user101" 
WHERE ID =(SELECT ID FROM test1 LIMIT 1)

But to use the above sql I should know the column name (ID) and I don't have access to column name in my case.

I need a sql statement that does not require column name but updates only the first record.

Update:

I am using ruby @db.exec(query) to execute query

We pass different sql statements like below to the method which calls @db.exec(query)

update table1 set t1_name = "123" limit 1;
update table2 set something = "xyz" limit 1;
update table3 set som = "abc" limit 1;

now before calling @db.exec(query)

I want to modify the query such that the query won't use limit

In this case I have access to table name, column name that I want to update. But I don't have access to any other column names (ID).

How do you know you want to change column test1 when you don't have access to the column names? — user330315
– user330315, Commented Oct 29, 2019 at 15:33
@a_horse_with_no_name I have updated my question I have access to table name and column name that I need to update. — Dot Net Dev
– Dot Net Dev, Commented Oct 29, 2019 at 15:42
This sounds like a terrible design - why don't you know the primary key of your tables? Updates that target a random row in a table seem like something very strange — user330315
– user330315, Commented Oct 29, 2019 at 15:45
This is just for our testing purpose, where we want to update only a single record data (any record). — Dot Net Dev
– Dot Net Dev, Commented Oct 29, 2019 at 15:48

user330315 · Accepted Answer · 2019-10-29 15:48:48Z

2

Although I think this approach has a very bad smell (what kind of application sends updates for random rows to the database), you can use the internal ctid column for this which is always available.

UPDATE test1 
  SET name = 'user101' 
WHERE ctid = (SELECT ctid FROM test1 LIMIT 1)

answered Oct 29, 2019 at 15:48

user330315

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres Update table with limit

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related