SQL - Update Select (Update a Table Based on Values in another Table)

A simple and quick snippet of SQL to update table values based on the values stored in another table — in other words, UPDATE SELECT. This code has only been tested in MySQL, similar code may be used in MSSQL or other database engines which I will try and add later.

UPDATE SELECT using JOIN Model:


UPDATE Dest
SET    Dest.Address1 = Src.Address1,
       Dest.Address2 = Src.Address2
FROM   SourceTableName Src
JOIN   DestinationTableName Dest ON Dest.PersonID = Src.PersonID


UPDATE SELECT using WHERE Model:


UPDATE DestinationTableName Dest, SourceTableName Src
SET    Dest.Address1 = Src.Address1,
       Dest.Address2 = Src.Address2
WHERE  Dest.PersonID = Src.PersonID

Comments (1)

RSS Collapse / Expand
+
+1
Well. Gary I don't know, but I think that this second example will work only with MySQL database. And you haven't written in which database this should work. I am shure that this won't work in SQL Server. I have checked in SQL Server manual msdn.microsoft.com/en-us/library/ms177523.aspx and I haven't found this kind of syntax you hav written.

I've got one better example of UPDATE ;)



update 
    gracze 
set
    drewno = drewno + drewno_kop * 2 + s.drewno
    ,kamienie = kamienie + kamienie_kop * 2 + s.kamienie
    ,krysztal = krysztal + krysztal_kop * 2 + s.krysztal
    ,siarka = siarka + siarka_kop * 1 + s.siarka
    ,klejnoty = klejnoty + klejnoty_kop * 1 + s.klejnoty
    ,rtec = rtec + rtec_kop * 1 + s.rtec
    ,zloto = zloto + zloto_kop * 1000 + s.zloto
from
    (
        select
            sum(ps.drewno) drewno
            ,sum(ps.kamienie) kamienie
            ,sum(ps.krysztal) krysztal
            ,sum(ps.siarka) siarka
            ,sum(ps.klejnoty) klejnoty
            ,sum(ps.rtec) rtec
            ,sum(ps.zloto) zloto
            ,ps.id_gracza
        from
            (
                select
                    id_gracza
                    ,sum(zloto_plus) zloto
                    ,sum(drewno_plus) drewno
                    ,sum(kamienie_plus) kamienie
                    ,sum(krysztal_plus) krysztal
                    ,sum(rtec_plus) rtec
                    ,sum(klejnoty_plus) klejnoty
                    ,sum(siarka_plus) siarka
                from
                    bohaterowie
                group by 
                    id_gracza
                
                union 
                
                select
                    b.id_gracza
                    ,sum(a.zloto) zloto
                    ,sum(a.drewno) drewno
                    ,sum(a.kamienie) kamienie
                    ,sum(a.krysztal) krysztal
                    ,sum(a.rtec) rtec
                    ,sum(a.klejnoty) klejnoty
                    ,sum(a.siarka) siarka
                from
                    bohaterowie b
                inner join
                    bohaterowie_artefakty ba
                on
                    ba.id_bohatera = b.id_bohatera and ba_pozycja < 15 and ba_pozycja > 0
                inner join
                    artefakty a
                on
                    a.id_artefaktu = ba.id_artefaktu
                group by
                    b.id_gracza
                
                union
                
                select
                    m.id_gracza
                    ,sum(case 
                        when 
                            m.glowny_budynek = 1 
                        then 
                            500
                        when 
                            m.glowny_budynek = 2 
                        then 
                            1000
                        when 
                            m.glowny_budynek = 3 
                        then 
                            2000      
                        when 
                            m.glowny_budynek = 4 
                        then 
                            4000
                        end) zloto
                    ,sum(mz.drewno) drewno
                    ,sum(mz.kamienie) kamienie
                    ,sum(mz.krysztal) krysztal
                    ,sum(mz.rtec) rtec
                    ,sum(mz.klejnoty) klejnoty
                    ,sum(mz.siarka) siarka
                from
                    miasta m
                inner join
                    magazyny_zasobow mz
                on
                    mz.typ_miasta = m.typ_miasta
                group by
                    m.id_gracza                    
                
                union
                
                select
                    id_gracza
                    ,0 zloto
                    ,0 drewno
                    ,0 kamienie
                    ,sum(case
                            when
                                magiczny_staw = 1 
                            then
                                2
                            else
                                0
                        end
                        )krysztal
                    ,0 rtec
                    ,0 klejnoty
                    ,0 siarka
                from
                    miasta      
                group by
                    id_gracza   
            ) ps
        group by
            ps.id_gracza
    ) s
where
    s.id_gracza = gracze.id_gracza
avatar

jmail

  • May 29, 2010, 6:08am

Only registered and authorized users can leave comments. Login or Register