Complex query to updating table, Help!!!!!

db , sql Malaysia
  • 11 years ago

    Table A
    ID    C2    C3    status
    --------------------
    1     20   21    AA
    2     30   30    AA
    3     31   31    BB


    Table B
    ID    C2    C3    Status
    --------------------
    1     20    21    Wa
    2     21    21    AB
    3     30    30    CC

    I have this case:
    So here is the case,

     I want to update tableB Colom Status with condition like this:

    search colom status in table A that equal to AA, compare col C1

    and C2 in table A with colom col1 and col2 in table B.
    example C1,C2 = 22,21 and there is a match in the first row in table
    If match, change status colom in table B with 'D'

    The problem is, i want to update the table by only using a single query, there will be no store procedure

     This is not a wrong table that i made up by my self, this is true. and some more is working at the banking database

     

    Thanks

    regards,

  • 11 years ago
    UPDATE Test2 SET Cat_up='test4' WHERE test2.Cat_a IN (SELECT Cat_a FROM Test1 WHERE Cat_state='sel'AND Cat_b=Test2.cat_b) AND Cat_b IN (SELECT Cat_b FROM Test1 WHERE Cat_state='sel' AND Cat_a=Test2.cat_a)

Post a reply

Enter your message below

Sign in or Join us (it's free).

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“Every language has an optimization operator. In C++ that operator is ‘//’”