Someone sent me this code.
WITH p AS
( SELECT ID, ROW_NUMBER() OVER (ORDER BY ID ASC) AS RN
FROM wp_posts
WHERE post_parent = 94341; )
UPDATE p SET menu_order = RN
That’s not great code. In fact, it won’t run. I know that, but does an AI? Let’s see how Copilot does.
This is part of a series of experiments with AI systems.
The First Test
I entered this prompt, but accidentally hit Enter. When I then pasted in the code and hit enter, I got this:
That is just about what I would have sent the author to let them know this code doesn’t work. It’s good to see a GenAI LLM can recognize this.
I then pasted in the entire prompt which was: “give me a table with the schema to match this code: WITH p AS ( SELECT ID, ROW_NUMBER() OVER (ORDER BY ID ASC) AS RN FROM wp_posts WHERE post_parent = 94341; ) UPDATE p SET menu_order = RN”
The Gen AI response was polite, with this answer:
Certainly! Based on your SQL code, it looks like you need a table schema that matches the structure of wp_posts, particularly including ID, post_parent, and menu_order. Here’s a possible schema:
I then had this code, with a copy button that I could copy/paste into SSMS.
CREATE TABLE wp_posts (
ID INT PRIMARY KEY, -- Unique identifier for each post
post_parent INT, -- Parent post ID (for hierarchical structures)
menu_order INT -- Order value for arranging posts
);
That’s what I would have done to mock this up for testing. You might guess the next step: data.
Here’s my prompt: create some test data for the table with Ids that increment but are not necessarily sequential and post_parent values that match the ID values
That got my this response.
That’s not bad, but it’s a limited set of data. Let’s ask for more.
Below this the GenAI LLM gave me a summary that explains what it did.
Next, I wanted a test. I used this prompt: write a tsqlt test to verify that the update statement works to reset the menu_order to sequential values for a particular post_parent
I got a nice response and the code I’m showing below the image.
Here is the code:
-- Step 1: Create a test class
EXEC tSQLt.NewTestClass 'TestMenuOrderUpdate';
GO
— Step 2: Define the test
CREATE PROCEDURE TestMenuOrderUpdate.[test_UpdateMenuOrder_Sequential]
AS
BEGIN
— Arrange: Fake the wp_posts table
EXEC tSQLt.FakeTable ‘dbo.wp_posts’;
INSERT INTO wp_posts (ID, post_parent, menu_order)
VALUES
(101, 94341, 5), (102, 94341, 3), (103, 94341, 8),
(104, 94341, 1), (105, 94341, 7);
— Act: Run the update logic
WITH p AS (
SELECT ID, ROW_NUMBER() OVER (ORDER BY ID ASC) AS RN
FROM wp_posts
WHERE post_parent = 94341
)
UPDATE wp_posts
SET menu_order = p.RN
FROM p
WHERE wp_posts.ID = p.ID;
— Assert: Check if menu_order is sequential
EXEC tSQLt.AssertEqualsTable ‘ExpectedResults’, ‘wp_posts’;
END;
GO
— Step 3: Create expected results table for assertion
CREATE TABLE ExpectedResults (
ID INT PRIMARY KEY,
post_parent INT,
menu_order INT
);
INSERT INTO ExpectedResults (ID, post_parent, menu_order)
VALUES
(101, 94341, 1), (102, 94341, 2), (103, 94341, 3),
(104, 94341, 4), (105, 94341, 5);
— Run the test
EXEC tSQLt.Run ‘TestMenuOrderUpdate’;
This is where I stopped. This code runs, though it’s brittle in that the Expected Results table is a real table and not a temp inside the test. I can easily edit that and likely quicker than the GenAI can. It’s copy/pasting some code for me from one spot to another. I also need to add a CREATE OR ALTER to the proc.
Summary
A quick use of an AI that easily saved me 5-10 minutes to mock of a scenario and show someone what they were doing wrong and also testing their code. I mock up things regularly to test them, and often do something like this to answer questions. This is a good place where AI can save some time, and might be worth using. I’m also glad to see it recognize and write some decent SQL code for me, especially the insert statement for test data.
Voice of the DBA 