complicated update (problem) - SQL Server Forums

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)

complicated update (problem)

Author

Topic

marek
Starting Member

34 Posts

Posted - 2010-03-18 : 05:15:17

Hallo all...

TabOrganization
Id
Name
Num

TabOrganization_EXT
Id
Money

I have 2 tab United TabOrganization and TabOrganization_EXT (ID - ID synchr.colum.) And I need to change the value (make update) TabOrganization_EXT the column "Money", but there is a problem. The value of "Money" should be changed by "NUM" because I have the sql code by "NUM" (not by ID).

This code does not work

SELECT
TabOrganization.ID,TabOrganization.NUM,TabOrganization.Name
FROM TabOrganization
  LEFT OUTER JOIN  TabOrganization_EXT ON TabOrganization_EXT.ID=TabOrganization.ID
update TabOrganization_EXT set Money=900 where num=245256 
update TabOrganization_EXT set Money=200 where num=345257 
update TabOrganization_EXT set Money=300 where num=352258
update TabOrganization_EXT set Money=650 where num=452259
.
.
.
.

I do not know how to type in syntax to update. I do not know whether it is possible
Thanks thanks for the idea

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-03-18 : 05:23:16

hi,

please provide some sample data and expected output..

marek
Starting Member

34 Posts

Posted - 2010-03-18 : 05:49:42

example:

First TabOrganization

ID.....NAME........NUM
1......SONY........245256
2......HP..........345257
3......NOKIA.......352258
4......PANASONIC...452259

Second tab. TabOrganization_EXT

ID......MONEY
1.......NULL
2.......NULL
3.......NULL
4.......NULL

after query: (I know that this query is wrong (especially update ...) so I need help)

SELECT
TabOrganization.ID,TabOrganization.NUM,TabOrganization.Name
FROM TabOrganization
LEFT OUTER JOIN TabOrganization_EXT ON TabOrganization_EXT.ID=TabOrganization.ID
update TabOrganization_EXT set Money=900 where num=245256
update TabOrganization_EXT set Money=200 where num=345257
update TabOrganization_EXT set Money=300 where num=352258
update TabOrganization_EXT set Money=650 where num=452259

output: (final result)

TabOrganization

ID.....NAME........NUM........MONEY
1......SONY........245256.....900
2......HP..........345257.....200
3......NOKIA.......352258.....300
4......PANASONIC...452259.....650

Thanks !!!

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-03-18 : 06:00:14

]try this and see.


SELECT
t1.Id
t1.Name
t1.Num,
case when t1.num=245256  then 900, 
      when t1.num=345257 then 200, 
when t1.num=352258 then 300, 
when t1.num=452259 then 650 else null
end as money from TabOrganization as t1

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-18 : 06:16:00

-- For only a few updates you can do it like this
update TabOrganization_EXT set Money=900 where id=(select id from TabOrganization where num=245256)
update TabOrganization_EXT set Money=200 where id=(select id from TabOrganization where num=345257)
update TabOrganization_EXT set Money=300 where id=(select id from TabOrganization where num=352258)
update TabOrganization_EXT set Money=650 where id=(select id from TabOrganization where num=452259)

No, you're never too old to Yak'n'Roll if you're too young to die.

marek
Starting Member

34 Posts

Posted - 2010-03-18 : 06:37:50

Haroon2k9:

I will try your query.

marek
Starting Member

34 Posts

Posted - 2010-03-18 : 06:47:58

quote:
Originally posted by marek

quote:
Originally posted by webfred

-- For only a few updates you can do it like this
update TabOrganization_EXT set Money=900 where id=(select id from TabOrganization where num=245256)
update TabOrganization_EXT set Money=200 where id=(select id from TabOrganization where num=345257)
update TabOrganization_EXT set Money=300 where id=(select id from TabOrganization where num=352258)
update TabOrganization_EXT set Money=650 where id=(select id from TabOrganization where num=452259)

No, you're never too old to Yak'n'Roll if you're too young to die.

It is OK

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-18 : 06:50:16

fine

No, you're never too old to Yak'n'Roll if you're too young to die.

marek
Starting Member

34 Posts

Posted - 2010-03-18 : 07:19:10

small problem (uups)
and what if I have the TabOrganization 2 times NUM: example

ID.....NAME........NUM
1......SONY........245256
2......SONY........245256
3......NOKIA.......352258
4......PANASONIC...452259

and I want to do this query:
update TabOrganization_EXT set Money=900 where id=(select id from TabOrganization where num=245256)

query will stopped. Please have the following advice.

result:

ID.....NAME........NUM........MONEY
1......SONY........245256.....900
2......SONY........245256.....900
3......NOKIA.......352258.....300
4......PANASONIC...452259.....650

I beg you once more...

thanks

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-03-18 : 07:21:21

update TabOrganization_EXT set Money=900 where id in (select id from TabOrganization where num=245256)

marek
Starting Member

34 Posts

Posted - 2010-03-18 : 07:23:16

quote:
Originally posted by webfred

fine

No, you're never too old to Yak'n'Roll if you're too young to die.

kleines Problem
Lesen Sie meinen Beitrag über....danke

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-03-18 : 07:27:50

quote:
Originally posted by marek

quote:
Originally posted by webfred

fine

No, you're never too old to Yak'n'Roll if you're too young to die.
kleines Problem
Lesen Sie meinen Beitrag über....danke

what is this?
i dint get any?

marek
Starting Member

34 Posts

Posted - 2010-03-18 : 07:32:55

This is exactly what I need.....problem solved

Big thanks WEBFRED and HAROON2k9

Sorry...(I not noticed your contribution has been directed to "webfred")

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-03-18 : 07:45:04

quote:
Originally posted by marek

This is exactly what I need.....problem solved

Big thanks WEBFRED and HAROON2k9

Sorry...(I not noticed your contribution has been directed to "webfred")

Welcome:

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources