UPDATE MyTable SET Field1 = (SELECT Something FROM AnotherTable WHERE MyTable.Field2 = AnotherTable.AnotherField) WHERE ...

If you could write the requirements and perhaps an example, then the solutions could be targeted to your psecific problem.

See more:

hi
i want create stored procedure in sql2008 to update data from one table to another table help for this

thanks in advance

pallavi

thanks in advance

pallavi

This can be done in several ways. One way is to use join as described in earlier post, another could be to use correlated (or non-correlated) sub-queries. One simple example:

If you could write the requirements and perhaps an example, then the solutions could be targeted to your psecific problem.

UPDATE MyTable SET Field1 = (SELECT Something FROM AnotherTable WHERE MyTable.Field2 = AnotherTable.AnotherField) WHERE ...

If you could write the requirements and perhaps an example, then the solutions could be targeted to your psecific problem.

Permalink

Share this answer

Comments

mhwasim
18-May-11 9:44am

The above solution has 2 issues

1. The query SELECT Something FROM AnotherTable WHERE MyTable.Field2 = AnotherTable.AnotherField should not return more than 1 record

2. if No data found then Field1 will be poopulated with Null which I dont think so is rite solution.

1. The query SELECT Something FROM AnotherTable WHERE MyTable.Field2 = AnotherTable.AnotherField should not return more than 1 record

2. if No data found then Field1 will be poopulated with Null which I dont think so is rite solution.

Wendelius
21-May-11 14:20pm

Bullet 1 is correct but since there was no additional information fro the OP this is just an example. For the bullet 2: depending on the situation, null may/may not be the correct value for the update. Again, more information would be needed about the situtation.

Hi try this,

TableA has four columns: a, b, c, d (a is the primary key column)

TableB has five columns: a1, b1, c1, d1, e1 (a1 and b1 together constitute the primary key for this table)

The foreign key relationship between the two tables is based on A.a = B.a1

The data in these 2 tables is as follows:

I. TableA

a b c d

1 x y z

2 a b c

3 t x z

II. TableB

a1 b1 c1 d1 e1

1 x1 y1 z1 40

2 a1 b1 c1 50

The requirement is to write a SQL to update columns b, c and d in TableA from the columns b1, c1 and d1 from TableB where-ever the join condition satisfies and e1 > 40 in TABLEB.

SQL Server:

UPDATE TABLEA

SET b = TABLEB.b1,

c = TABLEB.c1,

d = TABLEB.d1

FROM TABLEA, TABLEB

WHERE TABLEA.a = TABLEB.a1

AND TABLEB.e1 > 40

GO

Results after the update:

a b c d

————————————

1 x y z

2 a1 b1 c1

3 t x z

TableA has four columns: a, b, c, d (a is the primary key column)

TableB has five columns: a1, b1, c1, d1, e1 (a1 and b1 together constitute the primary key for this table)

The foreign key relationship between the two tables is based on A.a = B.a1

The data in these 2 tables is as follows:

I. TableA

a b c d

1 x y z

2 a b c

3 t x z

II. TableB

a1 b1 c1 d1 e1

1 x1 y1 z1 40

2 a1 b1 c1 50

The requirement is to write a SQL to update columns b, c and d in TableA from the columns b1, c1 and d1 from TableB where-ever the join condition satisfies and e1 > 40 in TABLEB.

SQL Server:

UPDATE TABLEA

SET b = TABLEB.b1,

c = TABLEB.c1,

d = TABLEB.d1

FROM TABLEA, TABLEB

WHERE TABLEA.a = TABLEB.a1

AND TABLEB.e1 > 40

GO

Results after the update:

a b c d

————————————

1 x y z

2 a1 b1 c1

3 t x z

Permalink

Share this answer

Try this

Its An example

Its An example

create procedure sp_Updatedata ( @OrderId int, @ShipName varchar(50), @ShipCountry varchar(50), @ShipMethodId int ) as update Orders set ShipName=@ShipName,ShipCountry=@ShipCountry,ShipMethodId=@ShipMethodId where OrderId=@OrderId

Permalink

Share this answer

v2

Comments

Henry Minute
11-Apr-11 14:14pm

How does this help the OP update from one table to another?

How is this a stored procedure?

It is excellent that you want to help others but you should try to actually answer their question, or explain why you aren't. For example "I do not think a stored procedure is the best way to do what you want. Have you tried .......?"

How is this a stored procedure?

It is excellent that you want to help others but you should try to actually answer their question, or explain why you aren't. For example "I do not think a stored procedure is the best way to do what you want. Have you tried .......?"

Is this the code you are looking for

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

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

```
CREATE PROCEDURE [dbo].[SP_MARINE_SYSTEM_USER_REGISTER_LOGIN_INFORMATION]
(
@SystemUserID NVARCHAR(50)
)
AS
UPDATE TBL_DETAILS SET SYSTEMUSERID=@SystemUserID
RETURN
```

Permalink

Share this answer

Comments

Henry Minute
11-Apr-11 14:17pm

How does this answer the question asked?

Please see the comment to mahen25s answer.

Please see the comment to mahen25s answer.

I think this might help you...

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

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

```
UPDATE TABLEA
SET b = TABLEB.b1,
c = TABLEB.c1,
d = TABLEB.d1
FROM TABLEA, TABLEB
WHERE TABLEA.a = TABLEB.a1
AND TABLEB.e1 > 40
GO
```

Permalink

Share this answer

Comments

Henry Minute
11-Apr-11 14:17pm

This would be quite slow and inefficient but is far better than your other answer as it actually answers the question.

The only problem is that it is an almost exact copy of an answer given an hour before yours.

The only problem is that it is an almost exact copy of an answer given an hour before yours.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject,
20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8
+1 (416) 849-8900