Using the alias from the select in the WHERE clause leads to error : "Missing FROM-clause entry for a table"

Question

I'm a newbie at SQL and I can't manage to make the following sql query to work ! There seem to be an issue with the alias and the where clause but I don't find any workaround...

Could someone enlighten me ?

Table t_adresse (SCHEMA ab)
-------
  ad_nomvoie   |   ad_numero   |   action   |
  Privet Drive        4
  KameHouse St        12

Table t_adresse (SCHEMA poma)
 -------
  ad_nomvoie    |   ad_numero   |
  Privet Drive        8
  KameHouse St        12

After my update I would like to get this result below (if I had to run the select "defined" by "req1") :
 -------
  ad_nomvoie (from ab) |   ad_numero (from ab) | ad_nomvoie (from poma)  | ad_numero (from poma) | action
  Privet Drive                      4             Privet Drive                      8                M
  KameHouse St                      12            KameHouse St                      12

.

WITH req1 as (SELECT AA.ad_nomvoie, AA.ad_numero, BB.ad_nomvoie, BB.ad_numero, AA.action
FROM ab.t_adresse as AA
INNER JOIN poma.t_adresse as BB
ON AA.ad_code = BB.ad_code
ORDER BY AA.ad_numero ASC)

UPDATE ab.t_adresse
SET "action" = 'M'
FROM req1
WHERE AA.ad_nomvoie = BB.ad_nomvoie
AND AA.ad_numero == BB.ad_numero

There is no `aa` or `bb` defined outside the query in your `WITH` clause, and the `ORDER BY` is quite pointless. Moreover, there is no join clause between `t_adresse` and `repl`. The whole statement makes no sense at all. Please [edit] the question and describe what you want to achieve. — Laurenz Albe, Feb 02 '21 at 11:40
Which `num_voie` do you want to match in the CTE? You have two of them and it is not clear what logic you want. — Gordon Linoff, Feb 02 '21 at 13:58
Hi Gordon, I've edited my question again ! Hope it clarifies things up ! — wanderzen, Feb 02 '21 at 14:48

score 1 · Answer 1 · answered Feb 02 '21 at 11:46

Presumably, you want the CTE joined to the table being updated. That would be:

WITH req1 as (
      SELECT aa.ad_nomvoie, aa.ad_numero, pa.ad_nomvoie, pa.ad_numero, aa.action
      FROM ab.t_adresse aa INNER JOIN
           poma.t_adresse pa
           ON aa.ad_code = pa.ad_code
     )
UPDATE ab.t_adresse a
     SET action = 'M'
FROM req1
WHERE a.ad_nomvoie = req1.ad_nomvoie AND
      a.ad_numero = req1.ad_numero;

Note:

The order by is superfluous in the CTE.
Don't escape identifiers with double quotes. If you created the table with double quotes, then re-create the table! They just clutter queries.
Use meaningful table aliases. pa is an abbreviation of the table name; bb is meaningless.

I would guess that the CTE is not necessary. So, perhaps this does what you want:

UPDATE ab.t_adresse a
    SET action = 'M'
    FROM poma.t_adresse as pa
    WHERE a.ad_code = pa.ad_code;

However, without sample data and a clear explanation of the logic, I'm not 100% sure this is equivalent.

In the select query inside there are multiple column is evident with same name `aa.ad_nomvoie` and ` pa.ad_nomvoie` another one is `aa.ad_numero` and `pa.ad_numero` . Please check . :) — Srijon Chakraborty, Feb 02 '21 at 11:52

Srijon Chakraborty · Answer 2 · 2021-02-02T12:05:00.350

I think I have a solution for you. Please check =>
MY TABLE STUCTURE

CREATE TABLE t_adresseAA(ad_code varchar(20),ad_nomvoie varchar(20),ad_numero varchar(20),action varchar(20));
CREATE TABLE t_adresseBB(ad_code varchar(20),ad_nomvoie varchar(20),ad_numero varchar(20),action varchar(20));

INSERT DATA

INSERT INTO t_adresseAA VALUES('001','sdfsd','werwer','Action1');
INSERT INTO t_adresseAA VALUES('002','sdfsd111','werwer222','Action2');
INSERT INTO t_adresseAA VALUES('003','sdfsd111','werwer222','Action3');

INSERT INTO t_adresseBB VALUES('001','sdfsd','werwer','Action1');
INSERT INTO t_adresseBB VALUES('004','sdfsd111','werwer222','Action2');
INSERT INTO t_adresseBB VALUES('005','sdfsd111','werwer222','Action3');

FINAL QUERY

WITH req1 as (SELECT AA.ad_nomvoie ad_nomvoieA, AA.ad_numero ad_numeroA, BB.ad_nomvoie, BB.ad_numero, AA.action
FROM t_adresseAA as AA
INNER JOIN t_adresseBB as BB
ON AA.ad_code = BB.ad_code
)

UPDATE t_adresseAA
SET "action" = 'M'
FROM req1
WHERE req1.ad_nomvoieA = t_adresseAA.ad_nomvoie
AND req1.ad_numeroA = t_adresseAA.ad_numero;

SELECT * FROM t_adresseAA; -- This line is for checking that the data is updated or not.

NOTE: Code is written in postgresql v11. You check the code in DB Fiddle

Hey Linker, thanks for your reply ! Your query looks almost like what I'm looking for (except that my two tables belong to two different schemas) Why do you add ad_nomvoieA ? Can't I use directly AA.ad_nomvoie ? — wanderzen, Feb 02 '21 at 13:23
Ambiguous problem might arise for `AA.ad_nomvoie BB.ad_nomvoie` and `AA.ad_numero, BB.ad_numero` . CTE : req1 will have 2 columns with same name and it might create an error. However, I did not test that error arising situation. — Srijon Chakraborty, Feb 02 '21 at 13:29

Using the alias from the select in the WHERE clause leads to error : "Missing FROM-clause entry for a table"

2 Answers2