top button
Flag Notify
    Connect to us
      Facebook Login
      Site Registration

Facebook Login
Site Registration

How to wite an update query with table joins?

+2 votes
77 views
How to wite an update query with table joins?
posted Aug 8, 2014 by Khusboo

Share this question
Facebook Share Button Twitter Share Button LinkedIn Share Button

2 Answers

+1 vote
 
Best answer

this is Postgres UPDATE JOIN format:

UPDATE address 
SET cid = customers.id
FROM customers 
WHERE customers.id = address.id

Here's the other variations: http://mssql-to-postgresql.blogspot.com/2007/12/updates-in-postgresql-ms-sql-mysql.html

answer Aug 14, 2014 by Vrije Mani Upadhyay
+1 vote

UPDATE

table1
INNER

JOIN table2
ON

table1.field1 = table2.field2
SET

table1.field3 = table2.field4 WHERE ...... ;

answer Aug 16, 2014 by anonymous
Similar Questions
+4 votes

Table Name : Hist_table

 Shp_cd wt_grp  net_wt  tot_wt
   101   10       9      7
   102   20       8      2
   103   15       4      1

Factor_table

Fact_id fact_column factor
  1     wt_grp        2
  2     net_wt        5
  3     tot_wt        3

Note – this factor table contains rows as the column name of hist_table.
Now we have to update the Hist_table with the multiplied with the factors
For example wt_grp factor is 2 then we have to update all the wt_grp column of hist table as Hist_table.wt_grp * factor_table.factor
So the result should be

shpcd   wt_grp  net_wt  tot_wt
101     20        45    21
102     40        40    6
103     30        20    3
+1 vote

LookupTable:

userid, mobileid, startedate, enddate , owner
 1 , 1 , 12-12-2000, 01-01-2001, asd 
2 , 2 , 12-12-2000, 01-01-2001, dgs 
3 , 3 , 02-01-2001, 01-01-2002, sdg
 4 , 4 , 12-12-2000, 01-01-2001, sdg

UserInfoTable:

userid, firstname, lastname, address 
1 , tom , do , test 
2 , sam , smith , asds 
3 , john , saw , asdasda
 4 , peter , winston , near by

Mobile:

Mobileid, Name , number, imeinumber 

1 , apple , 123 , 1111111
 2 , nokia , 456 , 2222222
 3 , vodafone , 789 ,` 3333333

CallLogs:

id , Mobileid, callednumbers (string), date , totalduration 
1 , 1 , 123,123,321 , 13-12-2000 , 30 2 , 1 , 123,123,321 , 14-12-2000 , 30 3 , 2 , 123,123,321 , 13-12-2000 , 30 4 , 2 , 123,123,321 , 14-12-2000 , 30 5 , 3 , 123,123,321 , 13-12-2000 , 30 6 , 3 , 123,123,321 , 14-12-2000 , 30 7 , 1 , 123,123,321 , 13-01-2002 , 30 8 , 1 , 123,123,321 , 14-01-2002 , 30

I want a query which will return me the following:
firstname, lastname, mobile.name as mobilename, callednumbers (as concatinated strings from different rows in CallLogs table) and need it for year 2000 example:

firstname, lastname, mobilename, callednumbers 
tom , do , apple , 123,123,321, 123,123,321
 sam , smith , nokia , 123,123,321, 123,123,321
 peter , winston , apple , 123,123,321, 123,123,321

any help will be highly appreciated...

Contact Us
+91 9880187415
sales@queryhome.net
support@queryhome.net
#280, 3rd floor, 5th Main
6th Sector, HSR Layout
Bangalore-560102
Karnataka INDIA.
QUERY HOME
...