update field based on two fields in different tbls

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

SQL Server 2005 Forums

Transact-SQL (2005)

update field based on two fields in different tbls

Author

Topic

cedtech31
Starting Member

17 Posts

Posted - 2008-09-26 : 09:49:51

I have two fields in table1 named 'code' and 'ordered'
I have another field in table2 called 'IC_code'

I want to update the 'ordered' field in table1 to 'N' based on if table1 'code' field and table2 'IC_code' field are the same.

how is this done?

thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-26 : 09:51:31

[code]UPDATE t1
SET t1.ordered='N'
FROM table1 t1
INNER JOIN table2 t2
ON t2.IC_code =t1.Code[/code]

bjoerns
Posting Yak Master

154 Posts

Posted - 2008-09-26 : 09:51:57

What is the relation between table1 and table2?

bjoerns
Posting Yak Master

154 Posts

Posted - 2008-09-26 : 09:58:12

Could be as well

UPDATE t1
SET t1.ordered='N'
FROM table1 t1
INNER JOIN table2 t2
ON t1.PK = t2.FK
WHERE t1.Code = t2.IC_Code

cedtech31
Starting Member

17 Posts

Posted - 2008-09-26 : 10:05:11

there is not relationship between the two tables

cedtech31
Starting Member

17 Posts

Posted - 2008-09-26 : 10:08:19

bjoerns

UPDATE t1
SET t1.ordered='N'
FROM table1 t1
INNER JOIN table2 t2
ON t1.PK = t2.FK
WHERE t1.Code = t2.IC_Code

why are you using t1 and t2 is that mean table1 and table2?

so the table could be

UPDATE t1
SET t1.ordered='N'
FROM t1
INNER JOIN t2
ON t1.PK = t2.FK
WHERE t1.Code = t2.IC_Code

bjoerns
Posting Yak Master

154 Posts

Posted - 2008-09-26 : 10:09:51

So it is more a table1.code exists in table2, as Visakh guessed?

cedtech31
Starting Member

17 Posts

Posted - 2008-09-26 : 10:16:39

table2 was created by one of my vendors. It contains three fields code, description, and type, it looks like they didn't add a PK to the field. I imported the table into the database.

cedtech31
Starting Member

17 Posts

Posted - 2008-09-26 : 10:22:59

after some more research it looks like they are using all three columns at part of the PK

cedtech31
Starting Member

17 Posts

Posted - 2008-09-26 : 10:32:37

OK in the ON t1.PK = t2.FK part of the statement table one has two fields that make of the PK (f1, f2) and table2 has three fields that make up the FK (f1, f2, f3). doe I just state one of the fields for each table or do I have to state all?

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-26 : 10:40:12

quote:
Originally posted by cedtech31

OK in the ON t1.PK = t2.FK part of the statement table one has two fields that make of the PK (f1, f2) and table2 has three fields that make up the FK (f1, f2, f3). doe I just state one of the fields for each table or do I have to state all?

Thanks

you've to state fields that relate the two tables i.e whose values correspond in tables t1 & t2

bjoerns
Posting Yak Master

154 Posts

Posted - 2008-09-26 : 10:41:45

Can you show some sample data and tell us which lines are to be updated?

cedtech31
Starting Member

17 Posts

Posted - 2008-09-26 : 11:16:10

table1

pk-f1 pk-f2 code ordered
7193C8FC-6D05 1 0.0001 N
7193C8FC-6D06 11.2 0.0002 N
7193C8FC-6D07 33.4 0.0003 N
7193C8FC-6D08 11.2 0.0001 N
7193C8FC-6D09 34 0.0002 N
7193C8FC-6D10 23 1111 N
7193C8FC-6D11 17 0.00008 N
7193C8FC-6D12 56 0.0002 N

table2

descr-pk type-pk IC_code-pk
fever1 1 0.0001
fever2 1 0.0002
fever3 1 0.0003
fever4 1 0.0001

The field that is begin updated is the 'ordered' in table1 based on if the number in the 'code' field matches any on the numbers in the 'IC_code' field of table2. I used pk to state primary keys of each table. hope that celar things up.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-26 : 11:21:07

so you just want to know if that code value exists in any one record of table2? i cant see any related columns in table2 for table1.

bjoerns
Posting Yak Master

154 Posts

Posted - 2008-09-26 : 11:24:43

Yes. Matches any => where exists => Visakh's solution.

Note that Visakh's solution is equivalent to

UPDATE table1
SET ordered='N'
WHERE EXISTS(
    SELECT *
    FROM table2
    WHERE table1.Code = table2.IC_Code
)

cedtech31
Starting Member

17 Posts

Posted - 2008-09-26 : 12:36:05

bjoerns that worked but I wanted to know since the code field and the IC_code contains the same data except that the IC_code field does not comtain all the codes that are in the code field of table2 can I use a join statement to accomplish the same task? Basically each year I receive new codes and some of the old codes are no longer used so I have to update the tables. Just wondering if their is a way with the join function or is that not possible.

thanks again, I learned a lot from the responses

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-26 : 12:41:34

quote:
Originally posted by cedtech31

bjoerns that worked but I wanted to know since the code field and the IC_code contains the same data except that the IC_code field does not comtain all the codes that are in the code field of table2 can I use a join statement to accomplish the same task? Basically each year I receive new codes and some of the old codes are no longer used so I have to update the tables. Just wondering if their is a way with the join function or is that not possible.

thanks again, I learned a lot from the responses

you can do update by using join. but how do you want update to happen? can you illustrate?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources