All geek questions in one place

Can you use an INNER JOIN with a primary key?

I have two tables with a one-to-one relationship. Table 1 has a composite primary key consisting of 4 columns. The foreign key of table 2 is set to the primary key of table 1.

When I try to execute the following UPDATE statement, I get an error:

UPDATE Table2 SET column1 = fakeTable.c1 FROM Table2 INNER JOIN ( SELECT Table1.primaryKey , (Table1.column3 + Table1.column4) AS c1 FROM Table1 ) AS c1 ON Table2.foreignKey = fakeTable.primaryKey

Am I not allowed to refer to keys as if they were columns?

+4
source share
2 answers

No, you need to list all the fields separately. But you can avoid the subquery that you have ...

 UPDATE Table2 SET column1 = Table1.column3 + Table1.column4 FROM Table2 INNER JOIN Table1 ON Table2.foreignKey1 = Table1.primaryKey1 AND Table2.foreignKey2 = Table1.primaryKey2 AND Table2.foreignKey3 = Table1.primaryKey3 AND Table2.foreignKey4 = Table1.primaryKey4

EDIT

Reply to comment:
- I thought the whole point of keys was to avoid having to concatenate columns!

Keys are not a time-saving device; they are data protection devices.

The primary key is a unique identifier. I can be compound or not, but the important thing is that it is unique and not null.

The foreign key is also a data integrity device. It ensures that if the data refers to something in another table, it must actually exist in that other table.

+4
source

No, you cannot refer to keys as columns. You will need to specify all the columns in both PK and FK ... both in your sub select and in your join on .

+2
source

Source: https://habr.com/ru/post/1396442/

More articles:


All Articles