CODEWITHSUNDEEP
sql-serverstored-procedures
user225837
user225837

Reputation:

Stored Procedure for Updating a Column in Sql Server

I have a requirement to update a column with multiple values. The query looks like below.

Update table1 set column1 = (
  select value from table2 where table1.column0 = table2.coulmn
)

Is there any generalised stored procedure for a requirement like the above?

Upvotes: 1

Views: 953

Answers (3)

RickNZ
RickNZ

Reputation: 18654

SQL Server doesn't use "generalised stored procedures" for this kind of thing. It's up to you to build your own SP, composed using an appropriate parameterized UPDATE statement.

Upvotes: 0

anon
anon

Reputation:

Why would you want to update one table with information that is easily available in another? Seems like you are just guaranteeing that you are going to have to run this query every single time you perform an update, insert or delete against the camsnav table. Otherwise how are you going to keep them in sync?

Also, if you cannot guarantee that the sub-query will return exactly one row, it is probably safer to use the SQL Server-specific and proprietary update format:

UPDATE f SET nav = n.nav
FROM camsfolio AS f
INNER JOIN camsnav AS n
ON f.schcode = n.schcode;

Upvotes: 1

No Refunds No Returns
No Refunds No Returns

Reputation: 8336

short of creating a statement as a string and using the "execute" statement, I don't know of one. Generally "execute" is frowned on as it's a potential injection attack point.

Upvotes: 3

Related Questions