Check Multiple rows

Transact-SQL
1

Hello,

I have a table which is formatted in this way -

Col1 --- Col 2 Code --- Article

However One 'code' may be repeated multiple times but with a different Article. Its always unique though so I will never had a 'Code' with the same 'article' twice.

There are some 'default' articles (around 6 defaults) that I want to insert into the table so that every 'Code' has these default 'Article'.

However some of the Codes might already have these default Articles so I wish to ignore them.

I am scratching my head at how I can do this so looking for some help if possible.

2

Use a NOT EXISTS clause, somthing like this:

INSERT INTO Tbl
(Code, Article)
SELECT
	Code, Article
FROM
	(SELECT DISTINCT Code FROM Tbl) AS a
	CROSS JOIN
		( VALUES
			('DefaultArticle1'),
			('DefaultArticle2'),
			('DefaultArticle3'),
			('DefaultArticle4'),
			('DefaultArticle5'),
			('DefaultArticle6')
		) da (Article)
WHERE NOT EXISTS
(
	SELECT *
	FROM
		Tbl t
	WHERE
		t.Code = a.Code
		AND t.Article = da.Article
);
1 Like
3

Thanks, If I wanted to also insert into another column within the same table (E.g Column called QTY_2) but I always wanted the value to be '1' how could I do that?

4

UPDATE Tbl SET QTY_2 = 1;

It will set EVERY row in the table to have QTY_2 = 1. Before you run this command, be sure that that is what you want to do. If you want to set QTY_2 to 1 only for a subset of rows, DO NOT use this update statement.

5

Hi James, Really I only want to do the UPDATE to the same records that I am inserting in the query you gave above.

6

If you want to the value set at create time just add it to the insert, if it is an after thought, try something like:

BEGIN TRAN;
UPDATE Tbl 
SET QTY_2 = 1 
WHERE QTU_2 IS NULL;

ROLLBACK TRAN;
--COMMIT TRAN;
1 Like
7

Thanks guys for the help.