[YSQL] DML Table-Join Support: USING clause in delete and FROM clause in update

[nocaway]

USING in delete and FROM in update are for listing the tables that are to be accessed by WHERE clause. These clauses should be supported to allow join in the DML statements.

Examples:
DELETE FROM a_table USING another_table WHERE a_table.id = another_table.id;
UPDATE a_table SET col = xxx FROM another_table WHERE a_table.id = another_table.id;

[ajcaldera1]

I see that UPDATE FROM is documented for 1.2, but when I attempt to use it, I get a cryptic message:

alan=# CREATE TABLE product_segment (                                                                                                      ID SERIAL PRIMARY KEY,                                                                                                                 
segment VARCHAR NOT NULL,                                                                                                              discount NUMERIC (4, 2));

alan=# INSERT INTO product_segment (segment, discount)                                                                                 VALUES                                                                                                                                   
  ('Grand Luxury', 0.05),
  ('Luxury', 0.06),  
  ('Mass', 0.1);

CREATE TABLE product(  
 id serial primary key,
                                                                                                                 name varchar not null,                                                                                                                 price numeric(10,2),                                                                                                                   net_price numeric(10,2),                                                                                                               segment_id int not null);

alan=# INSERT INTO product (name, price, segment_id)                                                                                   VALUES ('diam', 804.89, 1),                                                                                                                ('vestibulum aliquet', 228.55, 3),                                                                                                     ('lacinia erat', 366.45, 2),                                                                                                           ('scelerisque quam turpis', 145.33, 3),                                                                                                ('justo lacinia', 551.77, 2),                                                                                                          ('ultrices mattis odio', 261.58, 3),                                                                                                   ('hendrerit', 519.62, 2),                                                                                                              ('in hac habitasse', 843.31, 1),                                                                                                       ('orci eget orci', 254.18, 3),                                                                                                         ('pellentesque', 427.78, 2),                                                                                                           ('sit amet nunc', 936.29, 1),                                                                                                          ('sed vestibulum', 910.34, 1),                                                                                                         ('turpis eget', 208.33, 3),                                                                                                            ('cursus vestibulum', 985.45, 1),                                                                                                      ('orci nullam', 841.26, 1),                                                                                                            ('est quam pharetra', 896.38, 1),                                                                                                      ('posuere', 575.74, 2),                                                                                                                ('ligula', 530.64, 2),                                                                                                                 ('convallis', 892.43, 1),                                                                                                              ('nulla elit ac', 161.71, 3);

alan=# UPDATE product                                                                                                                  SET net_price = price - price * discount                                                                                             
  FROM                                                                                       
                                            product_segment                                                                                                                        WHERE                                                                                                                                  product.segment_id = product_segment.id;
ERROR:  System column with id -1 is not supported yet

[nocaway]

Thanks @ajcaldera1 , it's a documentation error. I'll update the doc.
And yes, we need to fix error messages also.

[xvaara]

Is there any progress on this?

[jaki]

Close by commit 09d0f9c.