[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 264
  • Last Modified:

sql query syntax help update from a selection

I have table users and field 'import' which is an 'int', i need to update its value to 1 when:

--
SELECT a.UserId
FROM dbo.Partiescont a  
Inner join users b on b.userid = a.userid
INNER JOIN users z ON z.userid = a.partcontid
WHERE z.EmployerId = 15304
--

The above statement returns the 'id' of all records from 'users' table. 'userid' is the ID field of such table.

So something like:

Update users
set import = 1 where

and then use the results from the selection above to update those records ... how would the syntax be ?
0
Aleks
Asked:
Aleks
  • 6
  • 5
1 Solution
 
lwadwellCommented:
Which flavour of database are you using ... the syntax is different between them.  e.g. SQL Server, MySQL, Oracle, PostgreSQL, SQLite, DB2, ... etc.
0
 
AleksAuthor Commented:
SQL server
0
 
lwadwellCommented:
UPDATE b
SET import = 1
FROM users b
Inner join dbo.Partiescont a on b.userid = a.userid
INNER JOIN users z ON z.userid = a.partcontid
WHERE z.EmployerId = 15304
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
lwadwellCommented:
assuming it was the 'b' version of USERS you wanted to update ... it appears in the SQL twice.
0
 
AleksAuthor Commented:
I want to update z.import to 1
0
 
AleksAuthor Commented:
If I query i get 271 results:

SELECT *
FROM users b
Inner join dbo.Partiescont a on b.userid = a.userid
INNER JOIN users z ON z.userid = a.partcontid
WHERE z.EmployerId = 15304

but if I run this update I only get 214 updates .. why ?

UPDATE z
SET z.import = 1
FROM users b
Inner join dbo.Partiescont a on b.userid = a.userid
INNER JOIN users z ON z.userid = a.partcontid
WHERE z.EmployerId = 15304
0
 
lwadwellCommented:
what do you get from:
    SELECT COUNT(DISTINCT z.userid)
    FROM users b
    Inner join dbo.Partiescont a on b.userid = a.userid
    INNER JOIN users z ON z.userid = a.partcontid
    WHERE z.EmployerId = 15304
An update will only update the record once ... if there were duplicates in the return set to select number would be higher than the update count ... at least that is what I think might be happening.
0
 
AleksAuthor Commented:
214
0
 
AleksAuthor Commented:
So do you think i am safe with the updating and i wont miss any objects ?  would the duplicates be in the partiescont table ?
0
 
lwadwellCommented:
If the userid is a primary key or unique index ... then most likely the Partiescont table is introducing them

To see which ones:
    SELECT z.userid, COUNT(*)
    FROM users b
    Inner join dbo.Partiescont a on b.userid = a.userid
    INNER JOIN users z ON z.userid = a.partcontid
    WHERE z.EmployerId = 15304
    GROUP BY z.userid
    HAVING COUNT(*) > 1

I do not understand your database and its relationships enough to be more firm than 'I think so'.  If the underlying query produces all of the correct userid's to be updated - they all will be.
0
 
AleksAuthor Commented:
Thanks, very useful  :)
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now