开发者

update table without using update statement

can anyone tell that how to update s开发者_JS百科ome records of a table without using update statement. it is possible using select statement.


I don't think you can update the table without update statement.


It is not possible with a select statement.

You can delete a row and insert the same row + your changes which is in many ways like an update, but will cause lots of trouble with foreign keys.

Oh, and your DBA might kill you.


You can use

REPLACE INTO tablename(primary key, ...{rest of the columns in the table}) 
VALUES(the same primary key, new values );

This will delete the previous row and insert a new row with the same primary key and updated column values. Not so much worthwhile, but maybe there is some other way.


It depends what tools you are using and what you actually want to achieve.

  • There are libraries which allow you to update data you got from a select statement. (eg. ORM's like NHibernate, I think ADO.NET also). These libraries are writing the update statements for you
  • You can use functions or triggers which change data when you just perform a select statement. In these functions or trigger, you still have an update statement.
  • For security reasons, you have to make sure that nobody injects an update statement into your select statement. So it is not just save to only perform a select statement.


how to update some records of a table without using update statement.

Use a MERGE statement.

it is possible using select statement.

Logically, an update is a delete and an insert: INSERT INTO..SELECT to a staging table, modifying the data as appropriate, then DELETE then INSERT INTO..SELECT from staging table.


On the off chance you were asking how this happened when a module ran a select statement it created, then you need to read up on SQL injection. You cannot do an update without an update statment of some kind (includiing not only update but doing delete and then insert or useiing merge) and the user must have update permission on a table, but you can add an update to a select statement that is dymanically created if you haven't correctly parametized it to avoid SQL injection.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜