SQL 語法片段小記

個人使用的 SQL 語法幾乎都是最基本的, 以下記錄幾個最近使用 ActionScript 時用到的片段:

1. 如果某筆記錄已經存在, 則更新該記錄

var sql_xml:XML = <s>
 UPDATE "{TABLE_NAME}"
 SET {type}='{path}'
 WHERE sid=(
  SELECT sid FROM "{TABLE_NAME}"
  WHERE "year"={year} AND "month"={month} 
  AND "date"={date} AND "page"={page}
 );
 </s>;

2. 如果某筆記錄不存在, 則新增記錄

var sql_xml:XML = <s>
 INSERT INTO "{TABLE_NAME}" (year, month, date, page, {type})
 SELECT {year}, {month}, {date}, {page}, '{path}'
 WHERE NOT EXISTS (
  SELECT * FROM "{TABLE_NAME}" 
  WHERE "year"={year} AND "month"={month}
  AND "date"={date} AND "page"={page}
 );
 </s>;

2. 兩張表 "content" 和 "content_usage" 是以 INNER JOIN 的方式查詢。在變更 "content_usage" 而需要 "content" 的條件時

var sql_xml:XML = <s>
 UPDATE "content_usage"
 SET "open"=0, "open_date"=NULL
 WHERE EXISTS (
  SELECT * FROM "content"
  WHERE "cid"="content_usage"."cid" AND "lv" &gt;= {level}
 );
 </s>;