CODEWITHSUNDEEP
sql-servert-sql
Miguel Moura
Miguel Moura

Reputation: 39444

Change column value for all rows

I have the following tables:

create table dbo.Files (
  Id int identity not null constraint PK_Files_Id primary key (Id),
  Content varbinary (max) null,
  Name nvarchar (280) null
)

create table dbo.BookFiles (
  BookId int not null, 
  FileId int not null, 
    constraint PK_BookFiles_Id primary key (BookId, FileId)
)

alter table dbo.BookFiles
add constraint FK_BookFiles_BookId foreign key (BookId) references Books(Id) on delete cascade on update cascade,
    constraint FK_BookFiles_FileId foreign key (FileId) references Files(Id) on delete cascade on update cascade;

And some data as follows:

BookId    FileId    Id   Name    Content
1         1         1    image
1         2         1    image
2         3         2    image
3         4         3    image

Some books have 2 images (BookdId=1), others have 1 image (BookId=2,3).

I need to rename the names of all files to:

  1. "image1" for the first image of the book (lowest FileId);

  2. "image2" for the second image of the book if exists.

I know how to update the value in all rows:

UPDATE files SET name = "image1";

But I don't know how to set "image1" and "image2" as described.

Could someone, please, help me out?

Upvotes: 1

Views: 97

Answers (3)

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

Use subquery to filter out the books which have multiple images 

update t set t.name = case when fileid = 1 then 'ima1' else 'ima2' end 
from table t
where bookid in (
    select bookid from table
    group by bookid
    having  count(*) > 1
)

In order to update all files image you could use ranking function row_number()

with cte as
(
   select *, 
          row_number() over  (partition by b.BookId order by b.FileId) Seq 
   from BookFiles b
   inner join Files f ON f.Id = b.FileId
)
update cte set name = concat(name, Seq)

Upvotes: 1

PSK
PSK

Reputation: 17943

You can try with CTE. It is not specific for only 2 images with same id, it will work for any number of images you have for a book, the number will be appended accordingly.

    ;WITH CT AS
    (
      SELECT BOOKID, FILEID, ROW_NUMBER() OVER 
        (PARTITION BY BookId ORDER BY BOOKID) AS RN , NAME 
       FROM BookFiles BF INNER JOIN Files F ON F.Id = BF.FileId
    )
    UPDATE CT
    SET NAME= 'image' + CAST(RN AS VARCHAR(10))

Note: Updates you make to the CTE will be cascaded to the source table.

Upvotes: 0

Abhishek
Abhishek

Reputation: 2490

Taking the data from your question, below code snippet would be able to give you the result as per your need -

declare @t table (BookId int,    FileId int,    Id int,   Name  nvarchar(280),   Content varbinary (max))
insert into @t values
(1,         1,         1,    'image',null),
(1,         2,         1,    'image',null),
(2,         3,         2,    'image',null),
(3,         4,         3,    'image',null)
;with cte as
(
select *, ROW_NUMBER() OVER(PARTITION BY BookId ORDER BY BookId) rn
from @t
)
select BookId,FileId,Id,(Name+cast(rn as nvarchar(1))) AS Name,Content from cte

Upvotes: 0

Related Questions