Help with a SQL query

Question

I have been trying different sql queries that do the following:

For all the table entries which have a field that has a field_value in clumn_a of table_1 which equals any field value in column_b of table_2 insert the id of the field in table_2 in id_of_the_other_entry_column of table_1.

However I can only use SQL and not use anything else than a SQL command for that. Is this even possible without the help of another programming language? If so.. any tips?

EDIT: An example:

TableOwner  
id  name
1   SomeCompany
2   SomeOtherCompany

TableContracts
Name               ownerid
NewCompany       Null --> Should remain null after the query
SomeCompany     Null -->Should change to 1 after the query
SomeCompany        Null -->Should change to 1 after the query
SomeOtherCompany   Null -->Should change to 2 after the query

thanks

Devin Burke · Accepted Answer · 2012-01-09 22:44:49Z

1

update tableContracts t2 set t2.ownerid=t1.id where exists( select t1.id from tableowner t1 where t1.name=t2.Name)

edited Jan 9, 2012 at 22:44

Devin Burke

13.8k12 gold badges60 silver badges85 bronze badges

answered Jan 19, 2011 at 8:22

user533276

341 bronze badge

Sign up to request clarification or add additional context in comments.

1 Comment

onedaywhen Over a year ago

I can't see how this can work: the scope of correlation name t1 is limited the subquery yet it is being used outside of the subquery.

Wowa · Accepted Answer · 2011-01-19 07:58:14Z

1

UPDATE
    table_2
SET
    id_of_the_other_entry_column = t1.ID
FROM
    table_2 t2
INNER JOIN
    table_1 t1
ON 
    t1.column_a = t2.column_b

EDIT:

Just tried this with MySQL, without success. With MySQL use:

UPDATE
    table_2 as t2
SET
    id_of_the_other_entry_column = (SELECT
                                        t1.ID
                                    FROM
                                        table_1 as t1
                                    WHERE
                                        t1.col_a = t2.col_b
                                    )

Importent: If table_1 contains more then one row with table_1.col_a equal to table_2.col_b the command will abort.

edited Jan 19, 2011 at 7:58

answered Jan 19, 2011 at 7:45

Wowa

1,8411 gold badge15 silver badges24 bronze badges

6 Comments

woolagaroo Over a year ago

But the select will return a set of many different ids since t1.col_a = t2.col_b is true for many different ids.

Wowa Over a year ago

Hint: If you want to make even more complex operations you don't need to use other languages. You also can create Stored procedure in many sql db's. MySQL: dev.mysql.com/doc/refman/5.0/en/create-procedure.html

Alexander Malakhov Over a year ago

@marco: to solve problem with multiple return rows just add LIMIT 1 (MySQL) or AND rownum = 1 (Oracle) to the end of subquery

Wowa Over a year ago

@Alexander Malakhov: in this case he doesn't get the a reference from table_2 to all table_1 rows. @marco: with one-to-many relation, the relation id must set to table_1, not to table_2

Alexander Malakhov Over a year ago

@Wowa: as I understood question, it OK

|

Collectives™ on Stack Overflow

Help with a SQL query

2 Answers 2

1 Comment

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related