{"id":39,"date":"2008-07-25T10:46:46","date_gmt":"2008-07-25T09:46:46","guid":{"rendered":"http:\/\/muratyaman.co.uk\/wp\/?p=39"},"modified":"2020-04-04T12:27:49","modified_gmt":"2020-04-04T11:27:49","slug":"deceiving-ms-sql-server-update-command","status":"publish","type":"post","link":"https:\/\/www.muratyaman.co.uk\/blog\/index.php\/2008\/07\/deceiving-ms-sql-server-update-command\/","title":{"rendered":"Deceiving MS SQL Server: UPDATE Command"},"content":{"rendered":"<p>First version:<\/p>\n<pre lang='sql'>\r\nUPDATE [MOVEMENT]\r\nSET [VAL] = [QTY] * ABS(MR.VAL \/ MR.QTY)\r\nFROM [MOVEMENT] MR\r\nWHERE --SOME CRITERIA\r\n      [REF] = MR.AREF\r\nAND [VAL] = 0\r\n<\/pre>\n<p>It does NOT complain and it seems all right, and runs without a problem, but since I selected the records with INNER JOIN and know that there <strong>are<\/strong> records that must be updated, we must find another way of updating those records. The problem is our table is referring to itself and the structure is identical, field names normally cause ambiguity problem in such queries.<\/p>\n<p>Second version (fully qualified names):<\/p>\n<pre lang='sql'>\r\nUPDATE [MOVEMENT]\r\nSET [MOVEMENT].[VAL] = [MOVEMENT].QTY * ABS(MR.VAL \/ MR.QTY)\r\nFROM [MOVEMENT] MR\r\nWHERE -- SOME CRITERIA\r\n      [MOVEMENT].REF = MR.AREF\r\nAND [MOVEMENT].[VAL] = 0\r\n<\/pre>\n<p>It will complain like this:<\/p>\n<blockquote><p>&#8220;The column prefix &#8216;MOVEMENT&#8217; does not match with a table name or alias name used in the query.&#8221;<\/p><\/blockquote>\n<p>Third version (sub-query) is the right solution to get around this problem and deceive MS SQL Server 2000:<\/p>\n<pre lang='sql'>\r\nUPDATE [MOVEMENT]\r\nSET [VAL] = [QTY] * Q.C1\r\nFROM ( -- ILLUSION OF A SUBQUERY PROVIDING DIFFERENT COLUMN NAMES\r\n    select MR.AREF AS AREF2\r\n    , ABS(MR.VAL \/ MR.QTY) AS C1\r\n    FROM [MOVEMENT] MR\r\n    ) Q\r\nWHERE --CRITERIA\r\n      [REF] = Q.AREF2\r\nAND [VAL] = 0\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>First version &#8230;<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[11],"tags":[56,33],"class_list":["post-39","post","type-post","status-publish","format-standard","hentry","category-technology","tag-ms-sql-server","tag-sql"],"_links":{"self":[{"href":"https:\/\/www.muratyaman.co.uk\/blog\/index.php\/wp-json\/wp\/v2\/posts\/39","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.muratyaman.co.uk\/blog\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.muratyaman.co.uk\/blog\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.muratyaman.co.uk\/blog\/index.php\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.muratyaman.co.uk\/blog\/index.php\/wp-json\/wp\/v2\/comments?post=39"}],"version-history":[{"count":2,"href":"https:\/\/www.muratyaman.co.uk\/blog\/index.php\/wp-json\/wp\/v2\/posts\/39\/revisions"}],"predecessor-version":[{"id":1009,"href":"https:\/\/www.muratyaman.co.uk\/blog\/index.php\/wp-json\/wp\/v2\/posts\/39\/revisions\/1009"}],"wp:attachment":[{"href":"https:\/\/www.muratyaman.co.uk\/blog\/index.php\/wp-json\/wp\/v2\/media?parent=39"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.muratyaman.co.uk\/blog\/index.php\/wp-json\/wp\/v2\/categories?post=39"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.muratyaman.co.uk\/blog\/index.php\/wp-json\/wp\/v2\/tags?post=39"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}