CODEWITHSUNDEEP
sqlsql-server-2008sql-update
CareerChange
CareerChange

Reputation: 669

Update table column using join

Can anyone help with this sql code, i'm trying to update the table column FirstURLForSite to 1 for the 1st match, but I'm struggling with the code, I'm only looking to update the 1st match.

Can anyone help, hopefully you can understand what i'm trying to do.

update [dbo].[at_WebsitesSearch]
set [FirstURLForSite] = 1
where(

left outer join
select min(ID) as rowid, [BaseURL]
from [dbo].[at_WebsitesSearch]
group by [BaseURL]) as FirstMatch on
[dbo].[at_WebsitesSearch].id = FirstMatch.rowid
where FirstMatch.rowid is null

Example of data

BasesURL                                FirstURLForSite
http://asia1x1.net/                         1
http://asia1x1.net/                         0
http://asia1x1.net/                         0

http://australia.bedandbreakfasts.net/      1
http://australia.bedandbreakfasts.net/      0
http://australia.bedandbreakfasts.net/      0

Upvotes: 2

Views: 84

Answers (2)

Praveen Nambiar
Praveen Nambiar

Reputation: 4892

You have to do something like this. Also check them Fiddle Demo below. 

;WITH CTE  
AS  
(  
    SELECT  BasesURL, FirstURLForSite,  
        ROW_NUMBER() OVER (PARTITION BY BasesURL   
                            ORDER BY BasesURL DESC) RN  
    FROM    at_WebsitesSearch  
)  
UPDATE  CTE  
SET     FirstURLForSite = 1  
WHERE RN = 1

SQL Fiddle Demo

Upvotes: 0

John Woo
John Woo

Reputation: 263723

Give this a try, I'm trying to fix your current query.

UPDATE  a
SET     a.FirstURLForSite = 1
FROM    at_WebsitesSearch a
        LEFT JOIN 
        (
            SELECT  MIN(ID) AS rowid, BaseURL
            FROM    at_WebsitesSearch
            GROUP   BY BaseURL
        ) FirstMatch
            ON FirstMatch.rowid = a.id
WHERE   FirstMatch.rowid IS NULL

UPDATE 1

WITH records
AS
(
    SELECT  BasesURL, FirstURLForSite,
            ROW_NUMBER() OVER (PARTITION BY BasesURL ORDER BY BasesURL ASC) rn
    FROM    at_WebsitesSearch
)
UPDATE  records
SET     FirstURLForSite = CASE WHEN rn = 1 THEN rn ELSE 0 END

Upvotes: 3

Related Questions