#1
  1. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Aug 2004
    Posts
    29
    Rep Power
    0

    Update query with group by clause


    Hi,

    I have two tables: Products and Photos

    I want to update Photo field(True/False) on products table. but only distinct records should be updated

    according to this example only 1,3,4,5 record ids should be updated

    Products Table:

    RecordId | ProductCode | Color | Photo
    1 1000 14
    2 1000 14
    3 1000 15
    4 1001 20
    5 1002 21


    Photos Table:

    ProductCode | Color
    1000 14
    1000 15
    1001 20
    1002 21

    thanks
  2. #2
  3. Contributing User
    ASP Skiller (1500 - 1999 posts)

    Join Date
    Mar 2005
    Location
    Columbus, OH
    Posts
    1,665
    Rep Power
    283
    try that
    Code:
    declare @Products as Table
    (
    RecordId int,
    ProductCode int,
    Color int,
    Photo varchar(1)
    )
    
    insert into @Products
    select 1, 1000, 14,'N'
    union
    select 2, 1000, 14,'N'
    union
    select 3, 1000, 15,'N'
    union
    select 4, 1001, 20,'N'
    union
    select 5, 1002, 21,'N'
    
    declare @Photos as Table
    (
    ProductCode int,
    Color int
    )
    insert into @Photos
    select 1000, 14
    union
    select 1000, 15
    union
    select 1001, 20
    union
    select 1002, 21
    
    ; with cte as
    (
    	select ROW_NUMBER() over(PARTITION by ProductCode, Color order by RecordId) as row,
    	*
    	from @Products
    )
    update P
    	set Photo = 'Y'
    from @Products P
    	join cte c
    	on c.RecordId = P.RecordId
    	join @Photos ph
    	on ph.ProductCode = p.ProductCode
    	and ph.Color = p.Color
    where c.row = 1
    GK
    __________________________________________________ _____
    if you found this post is useful click Give Rep button (bottom side on this reply ) and agree

Similar Threads

  1. Where clause in update query question
    By RadioactiveFrog in forum SQL Development
    Replies: 8
    Last Post: August 14th, 2007, 12:07 PM
  2. GROUP BY Clause
    By Pentium in forum ASP Development
    Replies: 5
    Last Post: March 14th, 2006, 03:57 AM
  3. count clause in sql query
    By mateenmohd in forum Microsoft SQL Server
    Replies: 1
    Last Post: June 8th, 2004, 08:21 AM
  4. Multi-Level Group By Clause Error
    By spearssc in forum Microsoft Access Help
    Replies: 1
    Last Post: January 19th, 2004, 11:50 AM
  5. Trying to get an Group By clause to work
    By Steve Schofield in forum SQL Development
    Replies: 1
    Last Post: February 6th, 2001, 08:13 PM

IMN logo majestic logo threadwatch logo seochat tools logo