4 ms·
This script works in SQL Server - I would think you could do the same in Postgres, but I'm not as familiar as I'd like to be with Postgres. Note that it does r
by a_m0d 11y ago
This script works in SQL Server - I would think you could do the same in Postgres, but I'm not as familiar as I'd like to be with Postgres.
Note that it does require 2 separate steps rather than 1 as you appear to desire, so may not work for you:
IF OBJECT_ID('dbo.dogs', 'U') IS NOT NULL
DROP TABLE dbo.dogs
GO
IF OBJECT_ID('dbo.doghouses', 'U') IS NOT NULL
DROP TABLE dbo.doghouses
GO
CREATE TABLE dogs (
id INTEGER IDENTITY (1, 1) PRIMARY KEY
,name VARCHAR(MAX) NOT NULL
);
-- Now we want to give each dog a doghouse:
CREATE TABLE doghouses (
id INTEGER IDENTITY (1, 1) PRIMARY KEY
,name VARCHAR(MAX) NOT NULL
);
GO
ALTER TABLE dogs ADD doghouse_id INTEGER REFERENCES doghouses (id);
GO
INSERT INTO dogs (name)
VALUES
('Sparky'),
('Spot')
;
DECLARE @DogsAndHouses TABLE (
doghouse_id INT
,name VARCHAR(MAX)
);
INSERT INTO dbo.doghouses (name)
OUTPUT INSERTED.id, INSERTED.name INTO @DogsAndHouses
SELECT
d.name
FROM
dbo.dogs d;
UPDATE d
SET
doghouse_id = dah.doghouse_id
FROM
dogs d
JOIN @DogsAndHouses dah ON d.name = dah.name;
GO
- CWuestefeld 11y agoYup, the OUTPUT keyword is the key to making this work. Before this was added to TSQL, the only way I knew of to make this work was by using a cursor and handling each record independently.