Databases
StackOverflow2013
Postsdbo
POUpdate an SQL table with its own rows with PostgreSQL
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POUpdate an SQL table with its own rows with PostgreSQL
    primarykey
    data
    text
    <p>I want to try to update a postgresql table with it's own values.</p> <p>This table contains an overview of the products sold by year and month.</p> <pre><code>CREATE TABLE sales ( sector character(3), brand character(4), product character(16), syear integer, smonth integer, units_sold integer, units_sold_year integer, CONSTRAINT pk_sales_id PRIMARY KEY (sector, brand, product, syear, smonth) ); INSERT INTO sales(sector, brand, product, syear, smonth, units_sold, units_sold_year) VALUES ('1', 'ABE', '71012', 2010, 0, 9, 0); /* The month 0 is the whole year */ INSERT INTO sales(sector, brand, product, syear, smonth, units_sold, units_sold_year) VALUES ('1', 'ABE', '71012', 2010, 1, 4, 0); INSERT INTO sales(sector, brand, product, syear, smonth, units_sold, units_sold_year) VALUES ('1', 'ABE', '71012', 2010, 2, 5, 0); INSERT INTO sales(sector, brand, product, syear, smonth, units_sold, units_sold_year) VALUES ('ALL', 'ABE', '71012', 2010, 0, 9, 10); ... </code></pre> <p>I've added the column 'units_sold_year' because I need to be able to do very quick requests on this table (I'd otherwise have to do subquerys) and I'm trying to fill it.</p> <p>Here's the update request I've built so far but it seems to run in an infinite loop :</p> <pre><code>UPDATE sales set units_sold_year = (SELECT units_sold_year FROM sales as s WHERE sector = 'ALL' and s.smonth = 0 and s.brand = su.brand and s.product = su.product and s.syear = su.syear) FROM sales su where su.syear = 2010 and su.brand = 'ABE' and su.product = '71012'; </code></pre> <p>Is it possile to update a table with its own rows like this ?</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USkevin
    1. USkevin
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COYes, it's quite possible, and I think you've made a good shot at it. How many rows are in your SALES table, and what indexes are present?
      singulars
      1. POUpdate an SQL table with its own rows with PostgreSQL
      1. USBob Jarvis
    2. COYour INSERT statements are broken - missing the column units_sold, but you've passed a value (6 columns, 7 values). Also, your values for 'product' (character (4)) are too long - they are 5 characters. If you are going to post code, make sure it works!
      singulars
      1. POUpdate an SQL table with its own rows with PostgreSQL
      1. USTimothy
    3. COOups, sorry for my mistakes (edited), I made up a simplified version here. I have 4 606 115 rows in sales. I have 2 indexes : CREATE INDEX idx_sector_brand_product_syear ON sales USING btree (sector NULLS FIRST, brand NULLS FIRST, product NULLS FIRST, syear NULLS FIRST); CREATE INDEX idx_sector_brand_product_syear_smonth ON sales USING btree (sector NULLS FIRST, brand NULLS FIRST, product NULLS FIRST, syear NULLS FIRST, smonth NULLS FIRST);
      singulars
      1. POUpdate an SQL table with its own rows with PostgreSQL
      1. USkevin
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload