CODEWITHSUNDEEP
sqljoingoogle-bigquerysql-update
Majid Rezaie
Majid Rezaie

Reputation: 189

How to use UPDATE JOIN correctly?

I tried to update a table row as below:

update EMPLOYEE set Salary=Salary * 1.15 where Dno=1;

which works fine, but I wanted to learn how to use UPDATE JOIN. so I use the Department Name instead of DNo.

I wrote below code:

update EMPLOYEE  set Salary=e.Salary * 1.15 from EMPLOYEE e join DEPARTMENT d on e.DNo=d.DNumber  where d.DName='Headquarters';

But the result for above code is that the SQL Command updates all the rows in EMPLOYEE table. Where did I do wrong?

Upvotes: 0

Views: 75

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270401

In BigQuery, change this to a filter in the where clause:

update EMPLOYEE e
    set Salary = Salary * 1.15 
    where exists (select 1
                  from DEPARTMENT d 
                  where d.DNumber = e.DNo and
                        d.DName = 'Headquarters'
                 );

The above is standard SQL and should work in any database. If you want a FROM clause, then use:

update EMPLOYEE e
    set Salary = e.Salary * 1.15 
    from DEPARTMENT d 
    where d.DNumber = e.DNo and
          d.DName = 'Headquarters';

There are (at least) two different ways of implementing FROM in an update, the "SQL Server method" and the "Postgres" method. In the SQL Server method, the table being updated should be in the FROM clause. In the "Postgres method", the table cannot be in the FROM clause. BigQuery adheres to the "Postgres method".

The above actually works in both methods, though.

Upvotes: 1

keval balar
keval balar

Reputation: 50

Try to use table alias name in update

update Emp
Set Salary = Emp.Salary * 1.15 
From EMPLOYEE As Emp
join DEPARTMENT As dep On Emp.DNo = dep.DNumber  
Where dep.DName = 'Headquarters';

Upvotes: 0

Related Questions