it is possible - 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

General SQL Server Forums

New to SQL Server Programming

it is possible

Author

Topic

shezad
Starting Member

32 Posts

Posted - 2011-01-03 : 02:08:32

Hi guys,

I have one query without using stored procedure it is
possible to update one field in a table with three diff
record for example

these are two record which is the result
of one out put i can be three ,4 no limit

M535489-14 5325014170008
MS35335-33 5310002090786

I have to update remark field

like
update tbl_abb
set remark = 'above information'

any idea

Shezad

MageshkumarM
Yak Posting Veteran

61 Posts

Posted - 2011-01-03 : 02:17:47

Hey,

You can't update 2 tables in single SQL.
Use stored procedure or transaction

In according to my knowledge, not possible...!

Regards
Mag...

shezad
Starting Member

32 Posts

Posted - 2011-01-03 : 02:21:13

no I want to update one table with three different record
in one field without removing previous detail and without using
stored procedure or cursor in single update statement

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-01-03 : 02:30:07

Not sure if I'm following the question...

You want to know if you can update one row in a table that has more than one row? If that's the question, sure you can. Look up details of the WHERE clause. Let's you filter which rows your select, update or delete applies to.

--
Gail Shaw
SQL Server MVP

MageshkumarM
Yak Posting Veteran

61 Posts

Posted - 2011-01-03 : 02:32:16

I think this is ur question which we are in.

for example :- 3 tables name called table1, table2, table3

you want to update one column in a table1, values in another table2,

Syntax:-

update table1 set column1 = table2.column1;

Let me know am in right r not..!

Suggestion welcome...,

Mageshkumar.M MCA

shezad
Starting Member

32 Posts

Posted - 2011-01-03 : 02:34:44

I think u did not understand my question i will write down the whole query

update tbl_workorder
set correctiveremarks = a.partno + +' '+ a.nsn
from

-------------
this query is return three record

55055T12
M535489-14 5325014170008
MS35335-33 5310002090786

select partno + +' '+ nsn 'Partno NSN' from tbl_workorderparts where workorderid in (
select workorderid from tbl_workorder where workorderclsdate is null and bbramsid in (
select itemuid from dbo.tbl_1149details where ref_pid in (select id from dbo.tbl_1149main
where id = 'C52EA6AE478A4CDEB43C624B28513DA3')))

-------------------------

where tbl_workorder.bbramsid = 'THU0Y9G'

now I want corrective remarks field get value like this

corrective remarks =
part NSN
55055T12
M535489-14 5325014170008
MS35335-33 5310002090786

I hope I made it clear

shezad
Starting Member

32 Posts

Posted - 2011-01-03 : 03:01:59

no body

Sachin.Nand

2937 Posts

Posted - 2011-01-03 : 03:20:13

You mean for these 3 records
55055T12
M535489-14 5325014170008
MS35335-33 5310002090786

You want to update just the 55055T12 record with part NSN ?

PBUH

shezad
Starting Member

32 Posts

Posted - 2011-01-03 : 03:21:38

no sachin I want to update
55055T12
M535489-14 5325014170008
MS35335-33 5310002090786

all three record for example if i will take cursor it will add one by one
these record in remarks field in place of that I want to update all three
record at once in remarks field

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-01-03 : 03:52:11

What do you want to update them to?

Can you clarify exactly what you want here, what the table design is, what the data looks like please.

btw, have patience. Bumping your post after less than 30 minutes is not necessary. We're all volunteers here, helping out in our spare time.

--
Gail Shaw
SQL Server MVP

MageshkumarM
Yak Posting Veteran

61 Posts

Posted - 2011-01-03 : 03:55:11

Here your answer Mr,

update sample3 set remark =( SELECT('55055T12'+'M535489-14 5325014170008'+'MS35335-33 5310002090786') )

syntax:-

update tbl_workorder set correctiveremarks = ( select (a.partno+' '+ a.nsn+''+a.id) )

pls check once...!

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2011-01-03 : 03:57:29

What I understand is that you want to update multiple records as single remark concatenated with line return.

I feel it can be done this way:
Concatenate multiple records as a single column based on some primary field in derived table.

Link to follow:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254

Then join the derived table with main table and run the update.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2011-01-03 : 04:03:03

quote:
Originally posted by MageshkumarM

Here your answer Mr,

update sample3 set remark =( SELECT('55055T12'+'M535489-14 5325014170008'+'MS35335-33 5310002090786') )

syntax:-

update tbl_workorder set correctiveremarks = ( select (a.partno+' '+ a.nsn+''+a.id) )

pls check once...!

Where you have defiend alias "a"

shezad
Starting Member

32 Posts

Posted - 2011-01-03 : 04:04:47

magesh understand what I m trying to say you but it does not work it says

Server: Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. The statement has been terminated.

* Pk_bohra it is for xml and other I just need one update statement

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2011-01-03 : 04:30:33

quote:
Originally posted by shezad

* Pk_bohra it is for xml and other I just need one update statement

Don't worry, It is not going to return you data in xml format.
'for xml ' is used for concatenating data internally and is one of the proven fastest way of concatenation.

The output that you get after concatenation, join that with the single update statement.

MageshkumarM
Yak Posting Veteran

61 Posts

Posted - 2011-01-03 : 04:36:03

Hey shezad,
Pls check this once...!

Sample Statements:-
-----------------
New Table created:-

create table table1 .. 3 (id int, remark varchar(50))

inserting:-
---------
insert into table1 values('1','55055T12');
insert into table2 values('1','M535489-14 5325014170008');
insert into table3 values('1','MS35335-33 5310002090786');

another table created
---------------------
create table sample3( id int, remark varchar(300))

Here update:-
************
update sample3 set remark = ((select t.remark from table1 t) +(select t.remark from table2 t)+(select t.remark from table3 t))where id=1

select * from sample3

could pls check this once...!

MAG,
SQL DB Developer.

shezad
Starting Member

32 Posts

Posted - 2011-01-03 : 04:40:37

it look too long to implement i feel better i will create one simple cursor

shezad
Starting Member

32 Posts

Posted - 2011-01-03 : 04:42:47

Magesh I am not using three table in short output of a simple query statement is
three record how to update three record at once on single table and single field

MageshkumarM
Yak Posting Veteran

61 Posts

Posted - 2011-01-03 : 04:43:25

ok

MAG,

Kristen
Test

22859 Posts

Posted - 2011-01-03 : 04:43:49

[code]update tbl_workorder set correctiveremarks = ...
[/code]
will update EVERY row in the tbl_workorder table - is that what you want?

If not then you also need a WHERE clause, e.g.:
[code]
update U
set correctiveremarks = U.partno + ' '+ U.nsn
from tbl_workorder AS U
where workorderid in (
select workorderid from tbl_workorder where workorderclsdate is null and bbramsid in (
select itemuid from dbo.tbl_1149details where ref_pid in (select id from dbo.tbl_1149main
where id = 'C52EA6AE478A4CDEB43C624B28513DA3')))
[/code]
That will set each record (selected by the WHERE clause) to have its [correctiveremarks] set to the Part Number plus SPACE plus [nsn]

I'm not sure where this bit fits in though:
[code]
where tbl_workorder.bbramsid = 'THU0Y9G'
[/code]

shezad
Starting Member

32 Posts

Posted - 2011-01-03 : 05:10:26

ok let me do last try if I can make this thing understandble

lets take one select * from abc which return three record
record 1
record 2
record 3

I have one table xyz which has field remarks

now I want to update xyz table like this
update xyz
set remarks = remarks + (record1,record2 ,record2) -- which is output of above query
where whatever condition

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources