CODEWITHSUNDEEP
sqlsql-server
gufran
gufran

Reputation: 111

how to update table with multiplication function?

when i tried update my table with this query 

update sekolah set [rata-rata] = 
(select ([total siswa]*12) from sekolah
where [class]='7IPA' and month([Date]) = 08) 
where [class]='7IPA' and month([Date]) = 08

a message is :

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

update my table is sekolah

my field is :

class month total siswa rata-rata

Upvotes: 0

Views: 2797

Answers (3)

Subash
Subash

Reputation: 895

UPDATE sekolah t1 JOIN sekolah t2 ON t1.class= '71PA' AND t2.month = '08' SET t1.rata-rata=(t1.[total siswa]*12)

Upvotes: 0

Shushil Bohara
Shushil Bohara

Reputation: 5656

If both are same table then it will be helpful:

UPDATE s SET s.rata-rata = (s.[total siswa]*12)
FROM sekolah s
where s.class='7IPA' and month(s.Date) = 08

OR

UPDATE sekolah SET 
rata-rata = ([total siswa]*12) WHERE class='7IPA' and month(Date) = 08

Upvotes: 2

Nava Bogatee
Nava Bogatee

Reputation: 1765

The error reason is obvious. Meaning, the nested query is fetching more than one row and Update statement is seeking only one value.

Two solutions

  1. Use SELECT TOP 1 statement to in the nested sql to fetch single value.

  2. Remove the duplicates from Class table.

    update sekolah set [rata-rata] = 
(select TOP 1 ([total siswa]*12) from class where [class]='7IPA' and     month([Date]) = 08) 
where [class]='7IPA' and month([Date]) = 08

Upvotes: 1

Related Questions