CODEWITHSUNDEEP
sqlsql-serversql-server-2008sql-updateinner-join
User7291
User7291

Reputation: 1115

Update table using inner join on several fields

i have this query:

update B
set B.NBR_OF_BACKUP=B.NBR_OF_BACKUP - 1
FROM BACKUP_TABLE B
INNER JOIN tbl I ON B.ID_BACKUP=i.id_backup

in the table tbl i have:

ID_BACKUP ID_IMAGES
1         2
1         3
1         4
6         3

my query is updating only for distinct id_backup but i need also to update the table BACKUP_TABLE as many times as the same id_backup is in the table tbl

Upvotes: 0

Views: 179

Answers (1)

Stuart Ainsworth
Stuart Ainsworth

Reputation: 12940

I think what you want is this:

update B
set B.NBR_OF_BACKUP=I.NBR_OF_BACKUP - 1
FROM BACKUP_TABLE B
INNER JOIN (SELECT id_backup, count(*) as nbr_of_backup FROM tbl GROUP BY id_backup) I 
ON B.ID_BACKUP=i.id_backup

Upvotes: 2

Related Questions