SQL UPDATE statment

Question

I have this query that returns me ids

select id, default_code from product_product ou
where (select count(*) from product_product inr
where inr.default_code = ou.default_code) > 1 and ou.active = false

but i'm getting syntax error with this statement

update product_product ou
where (select count(*) from product_product inr
where inr.default_code = ou.default_code) > 1 and ou.active = false set uo.default_code = uo.default_code || 'A';

ERROR:  syntax error at or near "where"
LINE 2:     where (select count(*) from product_product inr

how do i update correctly ids that i retrieve from first statement

@a_horse_with_no_name updated my question – Chaban33 Apr 17 '19 at 08:00 — Chaban33, Apr 17 '19 at 08:00

Fahmi · Answer 1 · 2019-04-17T08:15:39.853

2

You can try below - using JOIN

update product_product ou
set default_code = concat(ou.default_code, 'A')
from 
 (
   select inr.default_code,count(*) from product_product inr
    group by inr.default_code having count(*)>1
 )A
where A.default_code = ou.default_code and ou.active = false

edited Apr 17 '19 at 08:15

answered Apr 17 '19 at 07:53

Fahmi

37,315
5
22
31

You are missing a `HAVING` clause. – Tim Biegeleisen Apr 17 '19 at 07:54
ERROR: syntax error at or near "on" LINE 8: on A.default_code = ou.default_code – Chaban33 Apr 17 '19 at 07:59
ERROR: missing FROM-clause entry for table "uo" LINE 2: set uo.default_code = concat(uo.default_code, 'A') – Chaban33 Apr 17 '19 at 08:01
@Chaban33, you can checnk now – Fahmi Apr 17 '19 at 08:05
Still no `HAVING`? That code would be invalid if there is only 1 count. Might as well just write this: `update product_product set default_code = default_code || 'A'` – Michael Buen Apr 17 '19 at 08:13
Anyway, I think the OP is just looking for the right syntax for UPDATE from a result. I think he put `HAVING` in his final code – Michael Buen Apr 17 '19 at 08:13
Actually as i executed it it updated 174 records, but my queary returns only 71, so it's not working properly, sorry – Chaban33 Apr 17 '19 at 08:14
Because you forgot `HAVING`, that's all :) – Michael Buen Apr 17 '19 at 08:15
yeh i noticed that you insist on it :D need to google a bit about having , because don't know where to use it – Chaban33 Apr 17 '19 at 08:16
I provided an answer where to put it :) – Michael Buen Apr 17 '19 at 08:16
@Chaban33, I've added the having in the query - now i think it will work – Fahmi Apr 17 '19 at 08:16
Your description is wrong, you're not using `JOIN` :P If I remember correctly, Postgres does not allow `JOIN` in its UPDATE statement, only when it is in subquery. SQL Server does allow JOIN to its UPDATE table – Michael Buen Apr 17 '19 at 08:19
@MichaelBuen - chk here http://www.postgresqltutorial.com/postgresql-update-join/ – Fahmi Apr 17 '19 at 08:20
1

@fa06 You're correct, I revisited my old blog. UPDATE can be joined on Postgres. But it's convoluted when compared to SQL Server. SQL Server UPDATE+JOIN syntax is cleaner, it does not need a WHERE clause. UPDATE+JOIN would suffice in SQL Server. Postgres's UPDATE+JOIN need a WHERE clause: http://mssql-to-postgresql.blogspot.com/2007/12/updates-in-postgresql-ms-sql-mysql.html. – Michael Buen Apr 17 '19 at 08:28
@MichaelBuen, I've described becuase it's kind of updating using multiple tables - product to product the syntax may vary , but concept is same – Fahmi Apr 17 '19 at 08:29

Michael Buen · Accepted Answer · 2019-04-17T08:20:10.857

2

Correct:

update
    product_product ou
set
    default_code = ou.default_code || 'A'
from
    (
        select default_code
        from product_product
        group by default_code
        having count(*) > 1
    ) inr
where
    not ou.active
    and ou.default_code = inr.default_code

edited Apr 17 '19 at 08:20

answered Apr 17 '19 at 08:16

Michael Buen

38,643
9
94
118

ups.. ERROR: column "ou" of relation "product_product" does not exist LINE 4: ou.default_code = ou.default_code || 'A' – Chaban33 Apr 17 '19 at 08:19
@Chaban33 Corrected now. I forgot, you cannot put aliases on field to be set – Michael Buen Apr 17 '19 at 08:20
1

This will not work: `set ou.default_code = someValueHere`. This will work: `set default_code = someValueHere` – Michael Buen Apr 17 '19 at 08:22

SQL UPDATE statment

2 Answers2