SQL Update - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL Update

Author

Topic

riagil
Starting Member

4 Posts

Posted - 2010-08-15 : 06:31:56

Dear All, the best programmer...

I have 2 table : peminjaman and tmpinduk

peminjaman :
mulai | kembali | nis | induk | jml_buku | denda | status
13/8/2010 | 15/8/2010 | 21 | 144 | 2 | 100 | 0
13/8/2010 | 15/8/2010 | 21 | 145 | 3 | 100 | 0
14/8/2010 | 16/8/2010 | 22 | 146 | 2 | 100 | 0
13/8/2010 | 15/8/2010 | 23 | 147 | 2 | 100 | 0

tmpinduk :
induk | jdl_buku | pengarang | penerbit | jml_buku | status
144 | Naruto | Masashi | Elex | 1 | 1
145 | Bleach | Kung Chou | Gramedia | 1 | 1
146 | One Piece| Enola | Elexindo | 2 | 1
147 | Omega | Makabir | Estream | 1 | 1

my questions :
- How do I update tmpinduk from peminjaman, when I update, I hope my result as :

induk | jdl_buku | pengarang | penerbit | jml_buku | status
144 | Naruto | Masashi | Elex | 3 | 1
145 | Bleach | Kung Chou | Gramedia | 4 | 1
146 | One Piece| Enola | Elexindo | 4 | 1
147 | Omega | Makabir | Estream | 3 | 1

the simple is "jml_buku on peminjaman + jml_buku on tmpinduk ", then result is above...

help me please.....
I'm sorry with my english.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-08-15 : 06:53:19

update t
set t.jml_buku = t.jml_buku + p.jml_buku
from tmpinduk as t
join peminjaman as p
on p.induk = t.induk

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-15 : 10:49:46

can there be multiple records in peminjaman per jml_buku value?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

riagil
Starting Member

4 Posts

Posted - 2010-08-15 : 22:06:18

quote:
Originally posted by webfred

update t
set t.jml_buku = t.jml_buku + p.jml_buku
from tmpinduk as t
join peminjaman as p
on p.induk = t.induk

No, you're never too old to Yak'n'Roll if you're too young to die.

Hi webfred.. Thanks for your help, but i get this error...

"string or binary data would be truncated."
"the statement has been terminated."

riagil
Starting Member

4 Posts

Posted - 2010-08-15 : 22:07:12

quote:
Originally posted by visakh16

can there be multiple records in peminjaman per jml_buku value?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Yes there visakh16....

riagil
Starting Member

4 Posts

Posted - 2010-08-15 : 22:33:03

This is my complete table structure and content :
tmpinduk :
----------------------------------------------------------------
induk | jdl_buku | pengarang | penerbit |jml_buku |status |
----------------------------------------------------------------
144 | Cerita Rakyat | Iskandar | Bondan | 2 | 0 |
155 | Naruto Komik | Masashi Kishimoto | Elexmedia | 0 | 0 |
156 | Bleach Komik | Masashi Kishimoto | Elexmedia | 1 | 0 |
177 | Teori Pembangunan | Mulyanto | UI-PRESS | 0 | 0 |
179 | Ensiklopedia Hewan | - | Gramedia | 2 | 0 |
180 | Ensiklopedia Tumbuhan | - | Gramedia | 1 | 0 |
181 | Teknologi | Roy Suryo, MT | Ganeca | 2 | 0 |
182 | Baskeball Fundamental | Phil Jackson | Elexmedia | 1 | 0 |

peminjaman :
-----------------------------------------------------------------
| mulai | kembali | nis |induk | jml_buku | denda | status |
-----------------------------------------------------------------
16/8/2010 | 18/8/2010 | 6325 | 155 | 3 | 0 | 0 |
16/8/2010 | 18/8/2010 | 6325 | 156 | 2 | 0 | 0 |
16/8/2010 | 18/8/2010 | 6464 | 144 | 1 | 0 | 0 |
16/8/2010 | 18/8/2010 | 6464 | 177 | 1 | 0 | 0 |

the table above is a sample to imagine what I want...
Thanks a lot...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-16 : 05:16:37

sorry i meant per induk value. your sample shows only 1 record per induk value so no issues

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources