Combining different SQL requests but they all have same WHERE

I have lots of requests, but they always have the same WHERE, I done request to copy a character by typing his name from a table, so it copy the data from other tables (sometimes need to delete and copy, sometimes only update), but if I want to copy someone’s character, I have to type the names 7 times in total, and I wish I could type it once and it change for all

Here are the different requests:

request to delete all rows from “samymewtu” and insert all rows by copying someone else, here “myths”

DELETE i 
FROM items i
JOIN characters c ON c.characterId = i.characterId
WHERE c.name = 'samymewtu' --Delete the inventory where you are going to paste

INSERT INTO ITEMS (CHARACTERID, ITEMID, AMOUNT, SORT, EQUIPSLOT, MAXUPGRADE, CURRENTUPGRADE, STATMOD, ISTRADABLE, UNIQUEID, ATTACHED)
    SELECT  
        C2.CHARACTERID, I.ITEMID, I.AMOUNT, I.SORT, I.EQUIPSLOT,
        I.MAXUPGRADE, I.CURRENTUPGRADE, I.STATMOD, I.ISTRADABLE, I.UNIQUEID, I.ATTACHED
    FROM 
        ITEMS I 
    INNER JOIN 
        CHARACTERS C ON I.CHARACTERID = C.CHARACTERID 
                     AND C.NAME = 'Myths' --The character you want to copy his inventory
    INNER JOIN 
        CHARACTERS C2 ON C2.NAME = 'samymewtu' --The character copying the inventory of the character above

Here is the same but for the bank

DELETE i 
FROM bankItems i
JOIN characters c ON c.accountId = i.accountId
WHERE c.name = 'samymewtu' --Delete the inventory where you are going to paste

INSERT INTO bankItems (accountId, ITEMID, AMOUNT, SORT, MAXUPGRADE, CURRENTUPGRADE, STATMOD, ISTRADABLE, UNIQUEID, ATTACHED)
    SELECT 
        C2.accountId, I.ITEMID, I.AMOUNT, I.SORT, I.MAXUPGRADE,
        I.CURRENTUPGRADE, I.STATMOD, I.ISTRADABLE, I.UNIQUEID, I.ATTACHED
    FROM 
        bankItems I 
    INNER JOIN 
        CHARACTERS C ON I.accountId = C.accountId 
                     AND C.NAME = 'Myths' --The character you want to copy his  bank
    INNER JOIN 
        CHARACTERS C2 ON C2.NAME = 'samymewtu' --The character copying the bank of the character above

Here is to copy stats, but it update the row since there is only one and I keep the characterId

UPDATE s1 SET 
s1.exp=s2.exp,
s1.maxExp=s2.maxExp,
s1.totalExp=s2.totalExp,
s1.hp=s2.hp,
s1.maxHP=s2.maxHP,
s1.mp=s2.mp,
s1.maxMP=s2.maxMP,
s1.str=s2.str,
s1.def=s2.def,
s1.mag=s2.mag,
s1.dex=s2.dex,
s1.statByBonus=s2.statByBonus,
s1.usedStat=s2.usedStat,
s1.vitalityByLevel=s2.vitalityByLevel,
s1.vitalityByBonus=s2.vitalityByBonus,
s1.usedVitality=s2.usedVitality,
s1.spellByLevel=s2.spellByLevel,
s1.spellByBonus=s2.spellByBonus,
s1.usedSpell=s2.usedSpell,
s1.spellCapacity=s2.spellCapacity
FROM stats s1
CROSS JOIN stats s2
INNER JOIN characters c1 ON c1.characterId=s1.characterId
INNER JOIN characters c2 ON c2.characterId=s2.characterId
WHERE c1.name='samymewtu' --Character who must copy stats
AND c2.name='myths' -- The character from who you copy stats

There are some others but I think you get the idea, everytime I need to enter the c1.name=’samymewtu’ and the c2.name=’myths’

Answer

In SQL Server, you can use variables make a scriptquery dynamic.

DECLARE @C1 VARCHAR(100)
DECLARE @C2 VARCHAR(100)

SET @C1 = 'Myths'
SET @C2 = 'samymewtu'

DELETE i FROM items i
JOIN characters c ON c.characterId=i.characterId
WHERE c.name = @C2 --Delete the inventory where you are going to paste

INSERT INTO ITEMS (CHARACTERID,ITEMID,AMOUNT,SORT,EQUIPSLOT,MAXUPGRADE,CURRENTUPGRADE,STATMOD,ISTRADABLE, UNIQUEID, ATTACHED)
SELECT C2.CHARACTERID,I.ITEMID,I.AMOUNT,I.SORT,I.EQUIPSLOT,I.MAXUPGRADE,I.CURRENTUPGRADE,I.STATMOD,I.ISTRADABLE,I.UNIQUEID,I.ATTACHED
FROM ITEMS I INNER JOIN CHARACTERS C 
ON I.CHARACTERID = C.CHARACTERID AND C.NAME = @C1 --The character you want to copy his inventory
INNER JOIN CHARACTERS C2 ON C2.NAME = @C2 --The character copying the inventory of the character above

Advertisement

Answer