Postgres Join Delete Query

SQL-99 does not allow to delete data in a table when using JOINS. The reasons for this are quite obscure. Maybe it was considered too complicated to implement. Therefore for safety reasons it was not part of the standard.

As a result, MySQL implemented this feature and PostgreSQL rejected it.

The following query works under MySQL but fails under PostgreSQL:

 

DELETE h
FROM history AS h 
INNER JOIN term_node AS tn ON (h.nid = tn.nid) 
INNER JOIN term_data AS td ON (td.tid = tn.tid) 
WHERE h.uid = 2067 AND td.vid = 2
Advertisements

Sql Inner Join Update Query

create table 1 and table 2

 

CREATE TABLE Table1
(
Id INT,
FruitName Varchar( 100)
);

CREATE TABLE Table2
(
Id INT,
FruitName Varchar( 100)
);

Insert into Table1 Values ( 1,’Mango’ ), ( 2, ‘Graps’), (3, ‘Banana’);
Insert into Table2 Values ( 1,’Mango’ ), ( 2, ‘vitthal’), (4, ‘Orange’);
SELECT * FROM Table1 ;
SELECT * FROM Table2 ;
SELECT *
FROM Table1 T1 Inner Join Table2 T2
ON T1. Id = T2 .Id;

update table2 set FruitName=null where id>1

UPDATE Table2
SET FruitName = t1.FruitName
FROM Table1 as t1 INNER JOIN Table2 T2
ON T1. Id = t2.Id;

PostgreSQL SELECT

Select distinct rows by using DISTINCT operator.
Filter rows by using WHERE clause.
Sort rows by using the ORDER BY clause.
Select rows based on various operators such as BETWEEN, IN and LIKE.
Group rows into groups by using GROUP BY clause
Apply condition for groups by using HAVING clause.
Join to another table by using INNER JOIN, LEFT JOIN, RIGHT JOIN clauses.

 

Types of joins

Joins help retrieving data from two or more database tables. The tables are mutually related using primary and foreign keys.
Cross JOIN
Cross JOIN is a simplest form of JOINs which matches each row from one database table to all rows of another.
In other words it gives us combinations of each row of first table with all records in second table.
 Cross_Join

INNER JOIN

The inner JOIN is used to return rows from both tables that satisfy the given condition.

inner_Join

Inner_Join_Example

Inner_Join_Example_op

Natural JOIN

Natural Join is a type of Inner join which is based on column having same name and same datatype present in both the tables to be joined.

Natural_JoinNatural_Join_OP

Outer JOIN

Outer Join is based on both matched and unmatched data. Outer Joins subdivide further into,

  • Left Outer Join
  • Right Outer Join
  • Full Outer Join

Left Outer Join

The left outer join returns a result table with the matched data of two tables then remaining rows of the left table and null for the right table’s column.

Left_Outer_JoinLeft_Outer_Join_op

Right Outer Join

The right outer join returns a result table with the matched data of two tables then remaining rows of the right table and null for the left table’s columns.

Right_Outer_Join.PNG

Right_Outer_Join_op1.PNG

Full Outer Join

The full outer join returns a result table with the matched data of two table then remaining rows of both left table and then the right table.

Full_Outer_Join

Full_Outer_Join_OP.PNG