• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 185
  • Last Modified:

SQL update logic

i need to update using SQL columnA of table1 with value from either table2,table3,or table4 based on:
  if table1.columnB=table2.columnX and table1.columnC = table2.columnY
       table1.columnA = 0
else
  if table1.columnB=table3.columnX and table1.columnC = table3.columnY
      table1.columnA = 1
else
  if table2.columnB=table4.columnX and table1.columnC = table4.columnY

thanks.
0
jgordin
Asked:
jgordin
1 Solution
 
weellioCommented:

select case
            table1.columnB=table2.columnX
                  and table1.columnC = table2.columnY then
                        Update table1 set table1.columnA = 0
            case
            table1.columnB=table3.columnX
                  and table1.columnC = table3.columnY then
                        Update table1 set table1.columnA = 1
            case
            table2.columnB=table4.columnX
                  and table1.columnC = table4.columnY then
                        Update table1 set table1.columnA = 1
            else '?'      
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
CurrentDB.Execute "Update table1 T1 Set T1.columnA = fnColumnA(T1.columnB)"

And have following in a module:

Function fnColumnA(SomeValue as variant)As Variant
' will bw back with the reset of the function.
End Function

What are the variable types of the fields?

Mike
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
correction...

CurrentDB.Execute "Update table1 T1 Set T1.columnA = fnColumnA(T1.columnB, T1.columnC, T1.ID)"

Q1: whta are the vartype--v-----------v
              fnColumnA(T1.columnB, T1.columnC, T1.ID)
                       Q2: do you have a PK--------------^
                               to hook table1 to the other tables?

Mike

0
 
Patrick MatthewsCommented:
Hello jgordin,

UPDATE table1
SET columnA = (CASE
    WHEN table1.columnB = table2.columnX AND table1.columnC = table2.columnY THEN 0
    WHEN table1.columnB = table3.columnX AND table1.columnC = table3.columnY THEN 1
    WHEN table1.columnB = table4.columnX AND table1.columnC = table4.columnY THEN 0
    ELSE table1.columnA END)
FROM table1 LEFT JOIN
    table2 ON table1.columnB = table2.columnX AND table1.columnC = table2.columnY LEFT JOIN
    table3 ON table1.columnB = table3.columnX AND table1.columnC = table3.columnY LEFT JOIN
    table4 ON table1.columnB = table4.columnX AND table1.columnC = table4.columnY


Regards,

Patrick
0
 
jgordinAuthor Commented:
i ran:

UPDATE table3

set     colA  =
                   (CASE
                            WHEN t1.cA = t2.cA AND t1.cB = t2.cB THEN 2
                           ELSE 4
                  END)
from
          table1  t1,
        table2  t2,
        table3  t3
       
       
where
      t3.id = t1.id

The query doesnt update any rows. I would expect colA to be set to 4. Can you please let me know what is wrong with this update statement?

I ran "select count(*) from table3 t3,table1 t1 where t1.id = t3.id " and it returned rows.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now