I have table a ,table b with same columns .I want to replace the value in table b with table a value without using update keyword.
Asked
Active
Viewed 300 times
-3
-
Possible duplicate of [How can I do an UPDATE statement with JOIN in SQL?](https://stackoverflow.com/questions/1293330/how-can-i-do-an-update-statement-with-join-in-sql) – Vojtěch Dohnal Jul 26 '17 at 06:32
-
This is either homework or a misguided question, but in any case I think your only option would be to delete those records from the target table and then do an insert; but you would never do this in practice because of data consistency issues. – Tim Biegeleisen Jul 26 '17 at 06:33
-
1Possible duplicate of https://stackoverflow.com/questions/4646327/update-table-without-using-update-statement – Vikrant Jul 26 '17 at 06:33
-
Which RDBMS do you use? In SQL Server you might use [MERGE](https://technet.microsoft.com/en-us/library/bb522522(v=sql.105).aspx) – Ocaso Protal Jul 26 '17 at 06:33
-
2Possible duplicate of [update table without using update statement](https://stackoverflow.com/questions/4646327/update-table-without-using-update-statement) – Ocaso Protal Jul 26 '17 at 06:34
-
delete then insert – david25272 Jul 26 '17 at 06:46
-
1@ocasoprotal but, technically, merge statement uses update keyword.. :) – Caius Jard Jul 26 '17 at 06:48
-
DROP TABLEB; RENAME TABLEA TABLEB – Caius Jard Jul 26 '17 at 06:51
-
@CaiusJard D'oh you are correct. Too early in the morning and no coffee yet ;) – Ocaso Protal Jul 26 '17 at 07:00
1 Answers
1
The question could use a bit more detail on the table structure, what exactly you're trying to accomplish, and what precludes you from using UPDATE, but here goes:
CREATE TABLE #tempTable (col1, col2, col3, ...)
INSERT INTO #tempTable
SELECT
b.col1
, b.col2
, a.col3
, ...
FROM a
INNER JOIN b
ON a.col1 = b.col1
DELETE FROM b
WHERE col1 IN (SELECT col1 FROM a)
INSERT INTO b
SELECT
col1
, col2
, col3
, ...
FROM #TempTable
Which of course makes the bold assumption that Table a and b share a primary key, and that Table b doesn't have any constraint that would prevent deletion of matched rows. Please, provide some more detail and I'll update my answer accordingly.
Vocoder
- 144
- 5