MSSQL-쿼리팁 INSERT, UPDATE

INSERT ... SELECT

INSERT INTO #TABLE1(AAA,BBB,CCC) SELECT AAA,BBB,CCC FROM #TABLE2

UPDATE .... FROM

UPDATE #TABLE1

SET AAA=B.AAA

FROM #TABLE1 A, #TABLE2 B

WHERE A.IDX=B.IDX AND B.BBB=''

UPDATE #TABLE1

SET VALUE=TEMPTB.NDATE

FROM

(SELECT getdate() AS NDATE) AS TEMPTB)

UPDATE ... TOP

UPDATE #TABLE1

SET STATE = '00'

FROM (SELECT TOP 10 au_id FROM #TABLE1 ORDER BY au_name) AS X

WHERE #TABLE1.au_id = X.au_id

변수에 셋팅

DECLAER @ytd_sales INT

UPDATE #TABLE1

SET @yte_sales = qty = (SELECT SUM(year_qty) FROM #TABLE2 WHERE #TABLE1.eid = eid ADN sale_year = 2001)

WHERE eid=1

SELECT @yte_sales

SWAP

INSERT #Swap VALUES(23,90)

INSERT #Swap VALUES(33,21)

INSERT #Swap VALUES(05,01)

DECLARE @TEMP INT

UPDATE #Swap

SET @TEMP = value1, value1 = value2, value2 = @TEMP

SE:ECT * FROM #Swap

결과

signes