Question 89277 in PostgreSQL Question posted in
The official documentation can be found here.

Babelfish : UPDATE query with JOIN – Postgresql

I have the following sample data:

create table employee (id int,emp_name varchar(50),project_name varchar(50)); 
insert into employee(id,emp_name) values(1,'Smith');
insert into employee(id,emp_name) values(2,'Jill');
insert into employee(id,emp_name) values(3,'Hana');

create table employee_project (emp_id int,project_id int); 
insert into employee_project(emp_id,project_id) values(1,101);
insert into employee_project(emp_id,project_id) values(2,201);
insert into employee_project(emp_id,project_id) values(3,301);

create table project (id int,pro_name varchar(50)); 
insert into project(id,pro_name) values(101,'School');
insert into project(id,pro_name) values(201,'Tax');
insert into project(id,pro_name) values(301,'Road');

I need to update table employee column project_name.

Update Queries:

Try 1: Failed – Error : relation "e" does not exist

update e 
set project_name = p.pro_name 
from employee e
inner join employee_project ep on ep.emp_id = e.id
inner join project p on p.id = ep.project_id;

Try 2: Failed – Error : missing FROM-clause entry for table "p"

update employee 
set project_name = p.pro_name 
from employee e
inner join employee_project ep on ep.emp_id = e.id
inner join project p on p.id = ep.project_id;

Try 3: Works

update employee 
set project_name = p.pro_name 
from  employee_project ep 
inner join project p on p.id = ep.project_id
WHERE ep.emp_id = employee.id;

The try 3 works fine but not sure about SQL ANSI standard and also how will it gonna work for different joins(LEFT, RIGHT) when base table having number of different joins.

Note: I am running these queries from SQL Server Management Studio.

Tags:

2

Answers


  1. 0

    SQL Server accepts a special syntax for UPDATE with JOINs that does not exists in the ISO SQL standard.

    This syntax is quicker rather the official ones that use IN, ALL, ANY or EXISTS operators which are complicated to write…

    So this syntax is not available in some other RDBMS !

    WARNING : in the case of the UPDATE and given the cardinalities involved, the update may be ambiguous because it is arbitrary. Indeed, if the columns contained in the value expressions to update come from joined table(s) whose cardinality is greater than 1 for each row to update, then the value assigned will be any of the values resulting from the join !

    As an example in my next SQL book, I give the following SQL script :

    CREATE TABLE dbo.T_PERSONNE_PHYSIQUE_PSP
(PSP_ID               INT PRIMARY KEY,
 PSP_NOM_NAISSANCE    VARCHAR(64) NOT NULL,
 PSP_NOM_MARITAL      VARCHAR(64),
 PSP_PRENOM_USUEL     VARCHAR(32),
 PSP_DATE_NAISSANCE   DATE NOT NULL);

CREATE TABLE dbo.T_PRENOM_PRN
(PRN_ID      INT PRIMARY KEY,
 PRN_PRENOM  VARCHAR(32) NOT NULL UNIQUE);

CREATE TABLE dbo.T_PERSONNE_PHYSIQUE_PRENOM_PPP
(PPP_ID      INT IDENTITY PRIMARY KEY,
 PSP_ID      INT NOT NULL REFERENCES dbo.T_PERSONNE_PHYSIQUE_PSP (PSP_ID),
 PRN_ID      INT NOT NULL REFERENCES dbo.T_PRENOM_PRN (PRN_ID),
 PPP_ORDRE   TINYINT NOT NULL,
 UNIQUE (PSP_ID, PPP_ORDRE));

INSERT INTO dbo.T_PRENOM_PRN VALUES 
(1, 'Georges'), (2, 'Charles'), (3, 'André'), (4, 'Jean'), 
(5, 'Marie'), (6, 'Joseph'), (7, 'Benjamin'), (8, 'Raymond'),
(9, 'Marc'), (10, 'Paul'), (11, 'Jacques'), (12, 'Simone');

INSERT INTO dbo.T_PERSONNE_PHYSIQUE_PSP VALUES
(99, 'DE GAULLE', NULL, NULL, '1890-11-22'), -- Charles André Joseph Marie
(98, 'CLEMENCEAU', NULL, NULL, '1841-09-28'), -- Benjamin Georges
(97, 'POMPIDOU', NULL, NULL, '1911-07-05'); --Georges Jean-Raymond

INSERT INTO dbo.T_PERSONNE_PHYSIQUE_PRENOM_PPP VALUES
(99, 2, 1), (99, 3, 2),  (99, 6, 3), (99, 5, 4),
(98, 7, 1), (98, 1, 2),
(97, 1, 1), (97, 4, 2), (97, 8, 3);

UPDATE T
SET    PSP_PRENOM_USUEL = P.PRN_PRENOM
FROM   dbo.T_PERSONNE_PHYSIQUE_PSP AS T 
       JOIN dbo.T_PERSONNE_PHYSIQUE_PRENOM_PPP AS PP
          ON T.PSP_ID = PP.PSP_ID
       JOIN dbo.T_PRENOM_PRN AS P
          ON PP.PRN_ID = P.PRN_ID;

    The result will be :

    PSP_ID      PSP_NOM_NAISSANCE   PSP_PRENOM_USUEL
----------- ------------------- ----------------
97          POMPIDOU            Georges
98          CLEMENCEAU          Benjamin
99          DE GAULLE           Charles

    If I add the following index :

    CREATE INDEX X_PPP_PRN_PSP 
   ON T_PERSONNE_PHYSIQUE_PRENOM_PPP (PSP_ID, PRN_ID);

    The result is now :

    PSP_ID      PSP_NOM_NAISSANCE   PSP_PRENOM_USUEL
----------- ------------------- ------------------
97          POMPIDOU            Georges
98          CLEMENCEAU          Georges
99          DE GAULLE           Charles
    Login or Signup to reply.
  2. 0

    The errors you encountered occur in Babelfish v.2.3.x and before. In 2.4.0 or later, these have been fixed.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search