Improve UPDATE [CORE1896]

[firebird-automations]

Submitted by: Mercea Paul (m24paul)

Improvement for UPDATE syntax Instead : UPDATE Customers

SET Street = (SELECT Street FROM Moved AS m WHERE m.SSN = Customers.SSN), HouseNo = (SELECT HouseNo FROM Moved AS m WHERE m.SSN = Customers.SSN), City = (SELECT City FROM Moved AS m WHERE m.SSN = Customers.SSN) WHERE EXISTS (SELECT 1 FROM Moved AS m WHERE m.SSN = Customers.SSN);

to have the possibility to use :

UPDATE Customers SET (Street, HouseNo, City) = (SELECT Street, HouseNo, City FROM Moved AS m WHERE m.SSN = Customers.SSN) WHERE EXISTS (SELECT 1 FROM Moved AS m WHERE m.SSN = Customers.SSN);

or this can be achieved with MERGE?

[firebird-automations]

Commented by: Sean Leyne (seanleyne)

Why do you think that this "improvement" is required/necessary?

[firebird-automations]

Commented by: @asfernandes

Yes, you can achieve with MERGE using something like this: merge into customers c using ( select ssn, street, houseno, city from moved ) m on (m.ssn = c.ssn) when matched then update set street = m.street, houseno = m.houseno, city = m.city

Documentation for MERGE is missed in 2.1 release notes but is in doc/sql.extensions/README.merge.txt.

What you're suggesting here is the ROW datatype, useful for others things too.

[firebird-automations]

Commented by: Mercea Paul (m24paul)

Hi Sean I think that is always better to select one time(one subselect) an get 3 variable to update the row instead select 3 times(3 subselect) for same result. BUT,if MERGE do the trick ans Adriano said then is solved!

Thanks! ps. I found MERGE and works ok!

[firebird-automations]

Commented by: lacak (lacak)

FR for "Row value constructor" is already rgistered : CORE1383